Error in data table - filter yesterday's date

Help StudioX
, ,
1

Hi, I would like to filter yesterday’s date in the data table, the date is in the format: dd.MM.yyyy, I enter:

dashboard_dt.AsEnumerable().Where(Function(X) DateTime.ParseExact(X(“date”).ToString(), “dd.MM.yyyy”, System.Globalization.CultureInfo.InvariantCulture) = DateTime.Now.AddDays(-1)).CopyToDataTable()

but I get an error: String ‘10/28/2024’ was not recognized as a valid DateTime.

Do you have any idea what could be the cause?

2

Hello,

from my perspective, you got this error because of ParseExact method, if parsing is not possible an exception will be thrown ( unlike TryParse for instance).

Before investigating further I will ask you to ensure that your date is always is dd.MM.yyyy format ( you might have one row or more with dd/MM/yyyy format.)

3

@sullivanne,

This is because you are trying to parse MM/dd/yyyy to dd.MM.yyyy

Either change the format in your query or your date data to 28.10.2024

4

Thank You, in Excel I have the dd.MM.yyyy format, using the read range activity I save the table as a data table

5

I change formats and I get an error that there are no rows or the above

6

Hi @sullivanne Try below linq query this might work

dashboard_dt.AsEnumerable().Where(Function(x)
DateTime.TryParseExact(x(“date”).ToString(), “dd.MM.yyyy”,
System.Globalization.CultureInfo.InvariantCulture,
Globalization.DateTimeStyles.None, New DateTime()) AndAlso
DateTime.ParseExact(x(“date”).ToString(), “dd.MM.yyyy”, System.Globalization.CultureInfo.InvariantCulture) = DateTime.Now.AddDays(-1)
).CopyToDataTable()

7

@sullivanne,

This is tricky one as there is possibility of error very easily. As suggested get the confirmation from business what will be the format and stick to it.