How to filter Dates if datatable columns name is 'Date'

I have below Datatable ‘dt_MyTable’ having columns ‘CustomerID’, ‘Item’, ‘Date’. All of these columns are in String format.

I wanted to filter the records which are older than date ‘2019-12-01’.

image
I am doing as below:
1st Assign activity:
var_BaselineDate = Date.ParseExact(“2019-12-01”,“yyyy-MM-dd”,System.Globalization.CultureInfo.InvariantCulture)

where, var_BaselineDate is a variable of System.DateTime.

2nd Assign activity:
dt_TempResult = dt_MyTable.Select("(Date.ParseExact(Date.ToString.trim,‘yyyy-MM-dd’,System.Globalization.CultureInfo.InvariantCulture) < BaselineDate").CopyToDataTable

where, dt_TempResult is resulting datatable.

Here, I am getting below error:
“Filter Data: The expression contains undefined function call Date.ParseExact().”

I guess problem is Coulmn Name is ‘Date’ and it’s causing the issue where i use that column name in SELECT statement.

Please suggest how to handle this case? If the way i’m doing is incorrect, please correct me.

Have you tried doing the same using filter data table activity?

Hi @gopal_1981,

Try this by replacing your 2nd assign with the following.

dt_TempResult = dt_MyTable.Select("[Date] < '#"+BaselineDate.ToString("MM-dd-yyyy")+"#'").CopyToDataTable
1 Like

Thank you @HareeshMR for reply. It does not work with Filter Data Table.

Thank you @samir for reply.
your suggestion helped me to identify my issue. It did not work with ‘#’.
I removed # from your script and then used, it worked.
Here key point was [Date]; meaning I should have to use square bracket around ‘Date’ word.
Thank you.

Great!! :slightly_smiling_face: Welcome :+1: @gopal_1981