Issue in filtering date time column

[Muthulakshmi_Thangamuthu (Padman)]

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

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

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

[Lakshay_Verma]

DataTable filter conditions

What @Yoichi said is right. I just re-did what she have done. Follow steps Assign this in new DT dt = (From dr As datarow In dt Where DateTime.FromOADate(Double.Parse(dr.item("Start Date").ToString)).Date=Today AndAlso DateTime.FromOADate(Double.Parse(dr.Item("Approved On").ToString)).Date<=Today AndAlso DateTime.FromOADate(Double.Parse(dr.Item("Approved On").ToString)).Date>=New Date(Now.Year,Now.Month,1).AddMonths(-1) Select dr).copytodatatable Once you have new dt 'use for each row an…

Check this out… Similar use case

[Muthulakshmi_Thangamuthu (Padman)]

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

[Lakshay_Verma]

This for subtracting one month from current month

[Yameso (Jakub Swiderski)]

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

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”

[noufalahammed (Noufal)]

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)

[Muthulakshmi_Thangamuthu (Padman)]

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?/

[Yameso (Jakub Swiderski)]

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 If in that column can appear future date (like tomorrow) than u need to add two conditions
date>=Today and date < Today.AddDays(1)

[noufalahammed (Noufal)]

@Muthulakshmi_Thangamuthu

(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

Please modify to last part of the statement as below

now.AddDays(-1).Date

[Muthulakshmi_Thangamuthu (Padman)]

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

[noufalahammed (Noufal)]

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

[Muthulakshmi_Thangamuthu (Padman)]

Thanks much @noufalahammed It works…

[noufalahammed (Noufal)]

Please mark it as solution, it would help others