Comparing 2 date column in data table and filter the result into data table var

image

Above are the 2 Date columns in my excel sheet.
I want to Filter the records such as [Contract Start Date] < [Close Date]

Below is the expression I am using but it is returning the incorrect records along with correct ones.

(From d in in_dt_FilteredCGRecords.AsEnumerable Where d(“Contract Start Date”).ToString.Trim < d(“Close Date”).ToString.Trim Select d).CopyToDataTable

Please assist me
Thanks
Rushikesh

Hello @Athawale_Rushikesh Please try with the below one

in_dt_FilteredCGRecords.AsEnumerable.Where(Function(x) Convert.ToDateTime(x("Contract Start Date")).ToString("MM/dd/yyyy") < Convert.ToDateTime(x("Close Date")).ToString("MM/dd/yyyy")).CopyToDataTable

Hello @ushu
I am getting above error.
Changed the Close Date column name as per excel sheet in expression.

Thanks
Rushikesh

@Athawale_Rushikesh

you can also try this->

Dt.AsEnumerable.Where(Function(x) cdate(x("Column2")).ToShortDateString<CDate(x("Column")).ToShortDateString)

Here Column2 and column are input dt column name.

Refer the Execution →

image

Excel :
Reference.xlsx (13.1 KB)

Hello @Vaibhav_Rajpoot_17
Assign activity is throwing above error. I guess the Data table column i.e ([Contract Start Date] & [Close Date]) from my excel sheet is not a date type.

I am attaching the excel sheet for your reference.

Thanks
Rushikesh

@Athawale_Rushikesh

it seems that some of the cell have null value due to which exception is thrown.
Refer this Query its working fine →

dt_Refer.AsEnumerable.Where(Function(x) cdate(x("Close Date (Expected Signing or Date Won/Lost/WD)")).ToShortDateString<CDate(x("Contract Start Date")).ToShortDateString).CopyToDataTable

Refer The snap.

@Athawale_Rushikesh

First try to filter the input data .
Refer the filtered Excel and make sure Input datatable can not contain the Null values.
Reference.xlsx (13.2 KB)

@Vaibhav_Rajpoot_17
Your query is working fine since you interchanged the column names which also changes the resultant records.

I want below query to get is worked. But its giving incorrect records even after removing all the null values from both the column.

dt_Refer.AsEnumerable.Where(Function(x) cdate(x(“Close Date (Expected Signing or Date Won/Lost/WD)”)).ToShortDateString>CDate(x(“Contract Start Date”)).ToShortDateString).CopyToDataTable
(Changed the operator to >)
Number of records are approx 2000

Below are the result I am getting also which is incorrect.
image

@MarinAlexandru @prasath17 please have a look

Thanks,
Rushikesh

Hello,

This copies the rows from your DataTable ( if any ) that meet the condition to a new DataTable ( resultsDT ), otherwise just makes a New DataTable.

If (
( From results As DataRow In yourDT.AsEnumerable()
Where DateTime.Parse(results.Item(“Close Date (Expected Signing or Date Won/Lost/WD)”).ToString) > DateTime.Parse(results.Item(“Contract Start Date”).ToString)
Select results
).Count > 0 ,
( From results As DataRow In yourDT.AsEnumerable()
Where DateTime.Parse(results.Item(“Close Date (Expected Signing or Date Won/Lost/WD)”).ToString) > DateTime.Parse(results.Item(“Contract Start Date”).ToString)
Select results
).CopyToDataTable(),
New DataTable()
)

@MarinAlexandru
Thanks a lot for replying.
The solution worked for me.

Regards
Rushikesh

1 Like

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