DataTable filter conditions

[MLT]

hi i have a data table and it has start date column and approved on column and i want to select data which has start date is todays’ date and approved date ranges within previous month to current date. how to achieve this?. i got data for start date condition →
dt1.AsEnumerable().Where(Function(row) row(“Start Date”).ToString.Contains(Now.ToString(“yyyy-MMM-dd”))).ToArray and then i tried using and operator for the approved on condition but it does not work. Please help

[NIVED_NAMBIAR (NIVED N)]

Can u show how approved column looks like @MLT

[MLT]

Hi @NIVED_NAMBIAR This is the sample fileSample.xls (69 KB)

[Pradeep_Shiv]

Hi @MLT,

As you can see from the above image bot reading in different format, change the date format accordingly and use the below query to extract the require data from the datatable.

(From row in outSampleDt.AsEnumerable() Where row.Item(“Start Date”).ToString.Equals(Now.ToString(“dd-MM-yyyy”))).ToList()

Cheers
@MLT

[MLT]

hi pradeep, thanks for the suggestion. And yes i changed the date format and i could get the expected result but along with that i need to fetch for the column “Approved On” falls within the range from previous month to todays date using OR operator

[Pradeep_Shiv]

try this

(From row in outSampleDt.AsEnumerable() Where row.Item(“Start Date”).ToString.Equals(Now.ToString(“dd-MM-yyyy”)) Select row.item(“Month”)).ToList()

[Yoichi]

Hi,

Can you try the following?

dt.AsEnumerable.Where(Function(row) DateTime.FromOADate(Double.Parse(row("Start Date").ToString)).Date=Today).ToArray

Sequence.xaml (6.1 KB)

Regards,

[MLT]

hi, thanks for the help. In the given sample it fetches only the data which matches both the condition but in my case 2nd and 3rd row from “Approved On” column also have to be considered (Sample.xlsx) since it falls within the range of previous month to till date. i hope i’m clear

[Yoichi]

Hi,

How about the following?

dt.AsEnumerable.Where(Function(row) DateTime.FromOADate(Double.Parse(row("Start Date").ToString)).Date=Today AndAlso DateTime.FromOADate(Double.Parse(row("Approved On").ToString)).Date<=Today AndAlso DateTime.FromOADate(Double.Parse(row("Approved On").ToString)).Date>=New Date(Now.Year,Now.Month,1).AddMonths(-1)).ToArray

Regards,

[MLT]

thanks @Yoichi but its returning the result in text format.

[Yoichi]

Hi,

As @Pradeep_Shiv mentioned, data from xls is serial value of excel date format.
If you need date format, convert it to datetime type using DateTime.ToOADate method.

Or if we set Preserve Format property True in Read Range activity, data will be string of DateTime format and your first expression may work. However, we need to be aware this option is very slow.

Regards,

[Lakshay_Verma]

What @Yoichi said is right.
I just re-did what she have done. Follow steps

1. 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
and 2 assigns

assign 1
row("Start Date")=date.FromOADate(Double.Parse(row("Start Date").tostring))

Assign 2
row(“Approved On”)=date.FromOADate(Double.Parse(row(“Approved On”).tostring))

3. Write this new dt to your sheet