Create a column in the data table containing dates newer than two days

Help Activities
,
1

hello all,

  • I have a data table (dt_data)
  • There is a “Date_x” column in the table

Date_x
25.10.2024
18.10.2024
10.10.2024
19.10.2024
30.10.2024
28.10.2024
08.10.2024

  • I need to create a new data table with dates greater than the date two days before today’s date. (Today’s date is 21.10.2024, so the desired date is > 19.10.2024)

Date_y
25.10.2024
30.10.2024
28.10.2024

I would be happy if you could help me with this, thank you,

2

@alper_ozyurt

try this in assign

dt = dt.AsEnumerable.Where(function(x) Cdate(x("Date_x").ToString) > Now.AddDays(-1)).CopyToDataTable

Also it would be better to use if codnition first to check if alteast one row satisfies this condition …use dt.AsEnumerable.Where(function(x) Cdate(x("Date_x").ToString) > Now.AddDays(-1)).Count>0

on then side use assign with above…on else side no rows are present so can emtpy the datatable or reinitialize dt = New Datatable()

cheers

3

Hi @alper_ozyurt

Please find attached workflow
SampleWorkflow.zip (1.7 KB)

An Excel spreadsheet is shown with a single column labeled "Date_x" containing three dates in the "dd.mm.yyyy" format. (Captioned by AI)
An Excel spreadsheet is shown with a single column labeled "Date_x" containing three dates in the "dd.mm.yyyy" format. (Captioned by AI)1920×1020 55.2 KB

4

I tried with assign

dt = dt.AsEnumerable.Where(function(x) Cdate(x(“Date_x”).ToString) > Now.AddDays(-1)).CopyToDataTable`

error message = Assign: Conversion from string “” to type ‘Date’ is not valid.

5

Hi @alper_ozyurt
try with this
dt.AsEnumerable().Where(Function(row) DateTime.ParseExact(row(“Date_x”).ToString(), “dd.MM.yyyy”, System.Globalization.CultureInfo.InvariantCulture) > Now.AddDays(-2)).CopyToDataTable()

6

I’m so sorry. I just realized Date format in data table ; 10/25/2024 00:00:00

How should I edit assign?

7

Can you try
dt.AsEnumerable().Where(Function(row) DateTime.ParseExact(row(“Date_x”).ToString(), “dd.MM.yyyy”, System.Globalization.CultureInfo.InvariantCulture) > Now.AddDays(-2)).CopyToDataTable()

let me know if its working

8

not working. Date format in data table ; 10/25/2024 00:00:00

9

can try this one
dt_filtered = dt_data.AsEnumerable().Where(Function(row) DateTime.ParseExact(row(“Date_x”).ToString(), “MM/dd/yyyy HH:mm:ss”, System.Globalization.CultureInfo.InvariantCulture) > Now.AddDays(-2)).CopyToDataTable()

10

Assign: String was not recognized as a valid DateTime. :frowning:

11

I share the data
test.zip (225.8 KB)

12

@alper_ozyurt

you have empty values as well also check the formats accordingly

cheers

13

Hi, @alper_ozyurt

Fallow the steps below. If I helped you, please market it as solved.

Step 1
Assign: cutoffDate = DateTime.Now.AddDays(-2)

Step 2
Build Data Table: Create dt_filtered with a “Date_y” column.

Step 3
For Each Row in Data Table: Select dt_data.

Step 4
If:
DateTime.ParseExact(row(“Date_x”).ToString, “dd.MM.yyyy”, System.Globalization.CultureInfo.InvariantCulture) > cutoffDate

Step 5
Add Data Row: Add the row to the dt_filtered table.

In the end, you will have the dt_filtered table with the dates that are greater than two days before the current date. In your example, the dates greater than 10/19/2024 are:

10/25/2024
10/30/2024
10/28/2024

14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.