Date format validation on datatable using LINQ

Question

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

Posted 22-Jan-24 1:05am

Akshay malvankar

Updated 22-Jan-24 1:25am

v2

Add a Solution

Comments

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2024-01-22T01:28:00

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...)

Maciej Los · Answer 2 · 2024-02-22T07:54:00

Quote:
as seen above last 2 row in datable is not in valid "dd-mm-yyyy" format

Really?
Take a look at below example:

C#

List<string> lstOfDates = new List<string>()
{
	"22-07-2024",
	"23-09-2024",
	"2024-09-23",
	"1234-09-23"
};

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.");
	else
		Console.WriteLine($"{s} is NOT valid date.");
}

Result:

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[^]

Quote:
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:

C#

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[^]

Date format validation on datatable using LINQ

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0