Issue in filtering date time column

Hi,
Currently im working on a process where i need to filter two datetime columns.
Capture

This is the data table here i want to filter data [Sales Date] = DateTime.now.ToString() and [Action taken on] >= DateTime.now.Adddays(-1) and [Action taken on] <= DateTime.now.ToString()

And the below is the expected result

Output

I tried this but its not returning any values.Please help…
TestSample.xls (27.5 KB)

Check this out… Similar use case

Thanksfor the suggestion.
But couldyou explain me the last line New Date(Now.Year,Now.Month,1).AddMonths(-1)
Why do we use Now.Year,Now.Month,1

This for subtracting one month from current month

1 Like

This will always give you date equal to first day of previous month. You can place there your own date.

What is you business rule? Because in the second row you showed day in January, so its definitely not yesterday :wink:

If both conditions need to be fulfilled then u should use the operator “and”. But if one of them should be true then use operator “or”

You can use the below query in the Assign activity to filter according to your condition

(from i in sampleDt where DateTime.FromOADate(cdbl(i(“Sales Date”))).Date = now.date select i).toarray

Modify the condition and column names to apply the same for other logics

DateFilter.zip (29.2 KB)

:slightly_smiling_face:

1 Like

Hi Yes. Its an or condition since i want any one of them to be true. But if i want to filter yesterday to today, do i need to apply AddDays(-1)?

(From dr As datarow In DtP Where DateTime.FromOADate(Double.Parse(dr.item(“Initiated On”).ToString)).Date=Today Or DateTime.FromOADate(Double.Parse(dr.Item(“Action taken on”).ToString)).Date<=Today AndAlso DateTime.FromOADate(Double.Parse(dr.Item(“Action taken on”).ToString)).Date>=New Date(DateTime.Now.AddDays(-1))Select dr.ToArray

but it didint work… What mistake am i doing?/

Remember that dates has time too, so if u compare date = today than you will get only results like:

20/03/2021 00:00:00

Thats why there is >= operator :slight_smile: If in that column can appear future date (like tomorrow) than u need to add two conditions
date>=Today and date < Today.AddDays(1)

:wink:

Please modify to last part of the statement as below

now.AddDays(-1).Date

Thanks @noufalahammed i tried it but still its showing error

(From dr As datarow In DtP Where DateTime.FromOADate(Double.Parse(dr.item(“Initiated On”).ToString)).Date=Today Or DateTime.FromOADate(Double.Parse(dr.Item(“Action taken on”).ToString)).Date<=Today AndAlso DateTime.FromOADate(Double.Parse(dr.Item(“Action taken on”).ToString)).Date>=New Date(DateTime.Now.AddDays(-1).Date) Select dr.ToArray

Capture

Please use the below query, modify the condition if necessary

(from i in sampleDt where DateTime.FromOADate(cdbl(i("Sales Date"))).Date = now.date or DateTime.FromOADate(cdbl(i("Sales Date"))).Date <= now.date AndAlso DateTime.FromOADate(cdbl(i("Sales Date"))).Date >= now.AddDays(-1).Date select i).toarray

:slightly_smiling_face:

2 Likes

Thanks much @noufalahammed It works…

Please mark it as solution, it would help others

:slightly_smiling_face:

1 Like

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