Saving Dates in SQLServer

Question

hey guys

I have a legacy application where the input is a date string, i.e.:

06/12/2009

The format of the input is always a string, and is consistent, it's always dd/mm/yyyy

At the moment the legacy app just INSERTS this in a DateTime fields. Obviously if the Localization Culture settings of the Server change, we have a bug.

Two questions:

One:

Whats the safest way to store Dates in SQLServer in this situation?

Is there a format that will always be correctly interpreted regardless of the order of day and month?

Two:

What settings exactly determines the culture of a SQLServer DB, is it an OS setting, or a setting of that DB, or what?

cheers

Answer 1

Format YYYY-MM-DD is unambiguous, meaning that SQL Server won't confuse the month and day when converting a string value to DATETIME. (I've never experienced a problem with an implicit conversion using that format using the four digit year.)

The "safest" (and most convenient) way to store date values in SQL Server is to use DATETIME datatype.

Use the CONVERT function to explicitly specify the input and output formats when converting between DATETIME and strings.

SQL Server 2005 Documentation on CONVERT style argument values:

http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx

To convert a string representation to DATETIME datatype:

select CONVERT(datetime, '2009-06-03', 20)

The first argument is datatype to convert to, the second argument is the expression to be converted, the third argument is the style.

(style 20 is ODBC Canonical format = 'YYYY-MM-DD HH:MI:SS' (24 hour clock)

---

[FOLLOWUP]

To convert a DATETIME expression (e.g. getdate() to VARCHAR in 'YYYY-MM-DD' format:

select CONVERT(varchar(10), getdate(), 20)

Note that specifying varchar(10) gets you just the first 10 characters of the etnire 'YYYY-MM-DD HH:MM:SS' format.

[/FOLLOWUP]

---

As to what determines the default formats, that's going to be research. We avoid the issues caused by default formats by specifying the formats.

Answer 2

I would recommend storing all dates in UTC time when they are placed into the database. It will be consistent that way.

Storing dates like this seems to work well...

YYYY-MM-DD

Answer 3

See SET DATEFORMAT. The SQL 'culture' is set by SET LANGUAGE at a session level. SQL Server has its own date format settings, independent of the hosting OS. This is for several reasons: ANSI compliance, to prevent OS changes from affecting applications using the database hosted on that host and not least is compatibility, the SQL long predates the OS is currently running on.

Answer 4

I'm a bit conservative in these matters, but I prefer to use separate Year / Month / Day fields in the table, rather than a Date field that uses a DBMS-specific data type. It certainly takes more space, but the lack of ambiguity and increased portability is worth it to me.

The price you pay is that you don't get free date/time arithmetic and sorting, but it's easy enough to do yourself or by a slightly more complex "ORDER BY" clause.

Answer 5

Keep in mind that DATA is not its PRESENTATION. In this case that DATA is a DATE or DATETIME, regardless of how you show them.
As for inserting/updating/comparing datetime values, I quote the BOL:

When specifying dates in comparisons or for input to INSERT or UPDATE statements, use constants that are interpreted the same for all language settings: ADO, OLE DB, and ODBC applications should use the ODBC timestamp, date, and time escape clauses of:
{ ts 'yyyy-mm-dd hh:mm:ss[.fff] '} such as: { ts '1998-09-24 10:02:20' }
{ d 'yyyy-mm-dd'} such as: { d '1998-09-24' }
{ t 'hh:mm:ss'} such as: { t '10:02:20'}

I can assure you that, if you use this formats they will always work, regardless of the locale of you server