Joel Min Joel Min - 1 year ago 76
C# Question

Best practice for storing DateTime as string in SQL Server

Let's say I MUST store a

value as
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
on the
instance, and this will format the datetime according to the culture setting of the machine; and when retrieving the datetime, a simple
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
in different formats and this will error later when these values are parsed back to

I think a better approach would be to define a unified standard of the format of
(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 Source

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.