Saving Dates in SQLServer

Question

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.