Joel Min Joel Min - 3 months ago 12
C# Question

Best practice for storing DateTime as string in SQL Server

Let's say I MUST store a

DateTime
value as
string
in SQL Server in my web application. This web app will be deployed to multiple servers behind a LB and share the same DB server.

This is a legacy project and apparently the guys developed it simply called
ToString()
on the
DateTime
instance, and this will format the datetime according to the culture setting of the machine; and when retrieving the datetime, a simple
DateTime.Parse()
would parse it correctly with the same culture setting implicitly.

However I do not believe this is a good approach, as this creates a dependency between the data and the culture setting of the machine, it means if multiple devs have different timezone settings they will be inserting the
DateTime
in different formats and this will error later when these values are parsed back to
DateTime
.

I think a better approach would be to define a unified standard of the format of
DateTime
(e.g. dd/mm/yyyy), so that the data can work across different machines. However I'd like to know if there is any downside or unexpected issues with this approach and would greatly appreciate your insight!

Answer

The best format for storing a date/time as a string is an ISO 8601 standard format, either YYYY-MM-DD or YYYYMMDD (I prefer the version with the hyphens because I am a human being).

This format is standard, almost completely unambiguous (YYYYDDMM is almost unknown as a format). It also sorts correctly and can be used for between-type comparisons.