Filter datatable fetched from SAP ,future date should be present

[jain.priti26]

I tried to extract SAP data into datatable and got

so trying to filter out this way :
Curr_Date = DateAndTime.Today which returns “03/04/2024 00:00:00”
and then
dt_FilteredDT= dt1.AsEnumerable.Where(Function(x) Datetime.ParseExact(x(“Valid from”).ToString,“MM/dd/yyyy”,System.Globalization.CultureInfo.InvariantCulture)> Curr_Date).CopyToDatatable

but this is throwing error : Assign: String ‘23.01.2019’ was not recognized as a valid DateTime.

in this ‘23.01.2019’ is coming from x(“Valid from”)

Any suggestion to filter out datatable using linq query so rows with future date will be extracted

[rlgandu (Rajyalakshmi Gandu)]

@jain.priti26

Curr_Date = DateAndTime.Today.AddYears(-1)

' Filter the DataTable based on the date format "dd.MM.yyyy"
dt_FilteredDT = dt1.AsEnumerable.Where(Function(x) DateTime.ParseExact(x("Valid from").ToString(), "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture) > Curr_Date).CopyToDataTable()

[Parvathy (PS Parvathy)]

Hi @jain.priti26

It’s because the format of date is dd.MM.yyyy.

Try this:

Curr_Date = DateAndTime.Today.AddYears(-1)

dt_FilteredDT = dt1.AsEnumerable().Where(Function(x) DateTime.ParseExact(x("Valid from").ToString(), "dd.MM.yyyy", CultureInfo.InvariantCulture) > Curr_Date).CopyToDataTable()

Curr_Date is of DataType System.DateTime
dt_FilteredDT is of DataType System.Data.DataTable

Hope it helps!!

[Krishna_Raj]

@jain.priti26

It looks like the date format in your datatable column “Valid from” is in the format “dd.MM.yyyy” (e.g., ‘23.01.2019’). In your code, you’re trying to parse it using the “MM/dd/yyyy” format, which is causing the error.
check with expression
dt_FilteredDT = dt1.AsEnumerable.Where(Function(x) DateTime.ParseExact(x(“Valid from”).ToString, “dd.MM.yyyy”, System.Globalization.CultureInfo.InvariantCulture) > Curr_Date).CopyToDataTable

[jain.priti26]

Hello All,

I am trying to compare with curr_date which is coming in format MM/dd/yyyy 00:00:00 i.e. converting datatable value in MM/dd/yyyy…

May i try other way round means to convert curr_date format?

[Parvathy (PS Parvathy)]

Hi @jain.priti26

Use this

Filter datatable fetched from SAP ,future date should be present

Hi @jain.priti26 It’s because the format of date is dd.MM.yyyy. Try this: Curr_Date = DateAndTime.Today.AddYears(-1) dt_FilteredDT = dt1.AsEnumerable().Where(Function(x) DateTime.ParseExact(x("Valid from").ToString(), "dd.MM.yyyy", CultureInfo.InvariantCulture) > Curr_Date).CopyToDataTable() Curr_Date is of DataType System.DateTime dt_FilteredDT is of DataType System.Data.DataTable Hope it helps!!

DateTime.ParseExaxt method with convert the givent input format parameter to the format MM/dd/yyyy which you can compare with Curr_Date. Even when your date format is dd.MM.yyyy it will converted to the format MM/dd/yyyy format and compare it with Curr_Date variable.

Regard

[jain.priti26]

sorry…not working
giving error Assign: String ‘’ was not recognized as a valid DateTime.

[rlgandu (Rajyalakshmi Gandu)]

@jain.priti26

Curr_Date = DateAndTime.Today.AddYears(-1).ToString("MM/dd/yyyy")

' Filter the DataTable based on the date format "MM/dd/yyyy"
dt_FilteredDT = dt1.AsEnumerable.Where(Function(x) DateTime.ParseExact(x("Valid from").ToString(), "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture) > DateTime.ParseExact(Curr_Date, "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture)).CopyToDataTable()

[Parvathy (PS Parvathy)]

Hi @jain.priti26

Try this:

Curr_Date = DateTime.Today.AddYears(-1)

dt_FilteredDT = dt1.AsEnumerable().Where(Function(x) DateTime.ParseExact(x("Valid from").ToString().Trim(), "dd.MM.yyyy", System.Globalization.CultureInfo.InvariantCulture) > Curr_Date).CopyToDataTable()

Hope it helps!!

[jain.priti26]

still not working same issue

[jain.priti26]

still not working same issue…

[rlgandu (Rajyalakshmi Gandu)]

@jain.priti26

what is input format in your sap application, is in your sap application you have more formats,Please put all your possible formats and what is format you want to compare of Curr_Date please give us more inputs on that

[Parvathy (PS Parvathy)]

Hi @jain.priti26

If possible share the input excel with dummy data after extracting the data from sap and share that @jain.priti26 so that I can help you with query.

Regards

[jain.priti26]

in sap it is only displaying in dd.MM.yyyy format but i think when extracting in datatable it is coming as string

[rlgandu (Rajyalakshmi Gandu)]

@jain.priti26

Write the SAP Datatable data to a excel and put the sample

[jain.priti26]

EXPORT.XLSX (14.4 KB)
This is the data

[Parvathy (PS Parvathy)]

Hi @jain.priti26

Check the below workflow file

EXPORT.XLSX (15.2 KB)
Sequence12.xaml (11.0 KB)

Sheet1 is the given input and Sheet2 is output after bot run.

Regards

[jain.priti26]

Runs totally fine if I am using excel data…but if I am extracting data from SAP and then using that datatable then it is not working

[Parvathy (PS Parvathy)]

Hi @jain.priti26

Can you share the screenshot of SAP application please

Regards

[jain.priti26]