Specify DateTime format in DataTable DateTime column type

Question

While adding columns to DataTable we can specify which type of data the column will hold.

So this will restrict column to hold only valid date time

dataTable.Columns.Add("Date", typeof(DateTime));

But is there any way to specify which format or culture it should use while validating the date?

For me, it considers 31/12/1983 as a valid date because my culture has dd/MM/yyyy format.

But it fails the validation when I pass 12/31/1983 to it.

DateTime values don't have a format. They are simply binary numbers. Format is only an issue when converting to text, e.g. for display, or from text. If you're just storing DateTime values in a DataTable then format is completely irrelevant. Presumably you are entering text in a UI. That is where the formatting part is relevant so that's what you need to explain. How EXACTYLY is this data being entered and transferred to the DataTable? — jmcilhinney
– jmcilhinney, Commented Jul 31, 2024 at 9:09
What's the actual problem? You don't need any culture because there's no format. What does validating the date mean? Either the application displays the binary values using a format you didn't expect, or the original data was parsed using the wrong format. In any case, this has nothing to do with DataTable or DateTime — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 31, 2024 at 9:40
Assume people answering here have to import CSVs with dates from multiple cultures. Explain the actual problem, not your attempt to fix it. If the application used the wrong format to parse text data and got no parsing errors it may be too late to validate anything. All the DateTime values are valid by definition. You can check eg for future dates but you can't detect mismatches between 04/07/2023 and 07/04/2023. In such cases you need to know what the expected data looks like to be able to handle it - is it dates in the past N months? A specific range per file? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 31, 2024 at 9:49

shingo · Accepted Answer · 2024-08-01 08:17:09Z

2

You'd better pass a DateTime object instead of a string:

if(DateTime.TryParseExact(dateString, "dd/MM/yyyy", CultureInfo.InvariantCulture,
                          DateTimeStyles.None, out var date))
   dataTable.Rows.Add(...date...);

You can also set the Locale property to format strings:

// This needs to be done before the first data populating.
dataTable.Locale = new CultureInfo("en-gb");
dataTable.Rows.Add(...dateString...);

edited Aug 1, 2024 at 8:17

answered Jul 31, 2024 at 9:16

shingo

29.8k7 gold badges37 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Schmelter Over a year ago

Just to clarify: the Locale property is not used to format, sort or compare DateTime columns in the table, just strings. So it's not clear how it helps in this question.

shingo Over a year ago

The document is not clear, but the comment of this property mentions: "Also used for converting values to and from string". When you pass a string to a DateTime type column, this property is used for formatting.

Tim Schmelter Over a year ago

Seems you're right: dotnetfiddle.net/seB7ye But you mean "parsing" not "formatting".

Tim Schmelter · Accepted Answer · 2024-07-31 09:13:26Z

1

No, a DateTime has no culture or format, just a value: the total number of ticks, which are 100 nanoseconds, since epoch, which is 1970-1-1. Strings can be used to display formatted datetimes. But that's something completely different.

So this sounds like an XY-problem, you should not need to restrict the format. Just parse it from a string with this format and then add it to the DataTable. Or, if you have DateTimes, format them when you want to display them with DateTime.ToString(format).

answered Jul 31, 2024 at 9:13

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Collectives™ on Stack Overflow

Specify DateTime format in DataTable DateTime column type

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related