Click here to Skip to main content
15,881,424 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi convert data from excel to datatable now i want to validate the date format from datable using LINQ on datatable,

My datatable

Row id date
1 22-07-2024
2 23-09-2024
3 2024-09-23
4 12334-09-23

as seen above last 2 row in datable is not in valid "dd-mm-yyyy" format, i want to check that datable date column contain valid date format

What I have tried:

i tried many thing but not get succeed
Updated 22-Jan-24 1:25am
CHill60 22-Jan-24 7:14am    
Share at least one of your attempts and explain what happened
PIEBALDconsult 22-Jan-24 9:56am    
I definitely wouldn't do that (or anything else) with Linq.
At most you will get "can be interpreted as a date", but not necessarily the correct date (as has been mentioned).
Just loop through each of them, and pass each to DateTime.TryParse and DateTimeOffset.TryParse and note the results.

Quite probably, you can't.

Start by checking the excel source: unless the date is in a column that Excel recognises as a date (i.e. is formattable in Excel) then it's a string and can contain anything including valid dates, invalid dates, wrong format dates (mm/dd/yy instead of dd/mm/yy for example), and even the text "Hello world". If it's a string, there is pretty much nothing useful you can do with it: you can't rely on anything being the "original value".

For example, 01/02/03 is what date? 1st Feb 2003? 2nd Jan 2003? 3rd Feb 2001? All of them are valid interpretations in common use, and it depends on the person who entered it what date he expected it to be.

Once data gets into Excel or a database it lacks any "originator context" so you can;t tell what date was intended - it's too late to validate dates by that stage, and the request you made implies that it needs validation because you already know there is "bad data" in there. Nobody can fix that unless the date is the equivelant of "Hello world" - invalid dates could just be dates in a different format than you expected, or contain month names instead of numbers (and to add to the fun, in the OP's native language rather than English...)
Share this answer
PIEBALDconsult 22-Jan-24 9:52am    
As well as some the reasonable "never" or "N/A" as an expiration date or similar.
as seen above last 2 row in datable is not in valid "dd-mm-yyyy" format

Take a look at below example:
List<string> lstOfDates = new List<string>()

System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("De-de");
System.Globalization.DateTimeStyles styles = System.Globalization.DateTimeStyles.None;

foreach(string s in lstOfDates)
	DateTime d;
	if(DateTime.TryParse(s, out d) || DateTime.TryParse(s, ci, styles, out d))  
		Console.WriteLine($"{s} is valid date.");
		Console.WriteLine($"{s} is NOT valid date.");

22-07-2024 is valid date.
23-09-2024 is valid date.
2024-09-23 is valid date.
1234-09-23 is valid date.

I'd suggest to read this:
DateTime.TryParse Method (System) | Microsoft Learn[^]

i want to check that datable date column contain valid date format

No problem. :)
One way to achieve that is to create a list of possible formats and use them with this method: DateTime.TryParseExact Method (System) | Microsoft Learn[^]

Final note:
A date format depends on regional settings[^] user preferences. I do like yyyy-MM-dd format, but my colleagues prefer dd-MM-yyyy and official regional setting in Poland is...
You can check this out by using below code:
System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("PL-pl");
Console.WriteLine($"Short: {ci.DateTimeFormat.ShortDatePattern} \nLong: {ci.DateTimeFormat.LongDatePattern}");

More information you can find here: Format dates and times - Globalization | Microsoft Learn[^]
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900