Filtering the date column - from data table

[Sisay_Dinku]

Hi there,

I was trying to filter a couple columns from the data table. However, the date column is throwing an error " String was not recognized as a valid dat time "

Could you please help. Thanks

[Yoichi]

Hi,

Can you share current settings or expression?

Regards,

[Sisay_Dinku]

Hi @Yoichi
Here is the current expression

dt_DataSource.AsEnumerable.where(Function(x) x(“Customer Name”).ToString.Equals(“PACIFIC SUNWEAR OF CALIFORNIA”) And x(“Warehouse Id”).ToString.Equals(“09”) And DateTime.parseExact(x(“Start Dt”).ToString,“MM/dd/yyyy 00:00:00”,system.Globalization.CultureInfo.InvariantCulture).To
SourceData_2.xlsx (13.5 KB)
String(“dd/MM/yyyy”).Equals(“01/04/2023”)).CopyToDataTable

Also I want the leading zero removed from the “cust purchase order” column.

[Yoichi]

Hi,

For now, can you try to use DateTime.Parse instead of ParseExact, as the following?

dt_DataSource.AsEnumerable.where(Function(x) x("Customer Name").ToString.Equals("PACIFIC SUNWEAR OF CALIFORNIA") And x("Warehouse Id").ToString.Equals("09") And DateTime.Parse(x("Start Dt").ToString).ToString("dd/MM/yyyy").Equals("01/04/2023")).CopyToDataTable

Regards,

[Sisay_Dinku]

Hi @Yoichi

I still get the same error. Could there be other options?

Thanks!

[Yoichi]

Hi,

Probably because ReadRange reads extra blank rows. Can you try the following expression? This uses AndAlso instead of And.

dt_DataSource.AsEnumerable.where(Function(x) x("Customer Name").ToString.Equals("PACIFIC SUNWEAR OF CALIFORNIA") AndAlso x("Warehouse Id").ToString.Equals("09") AndAlso DateTime.Parse(x("Start Dt").ToString).ToString("dd/MM/yyyy").Equals("01/04/2023")).CopyToDataTable

Regards,

[Sisay_Dinku]

@Yoichi you’re the best.

Thanks a lot. It worked. I tested with the input files I have.
Maybe I will create another topic for the leading zero removal one (Cust Puchase Order Column). Is there any way we can do it at the same time? Thanks a lot!

[Sisay_Dinku]

FYI - the prior expression also worked when ‘Andalso’ added. Thanks for the insightful conversation.