The correct way to specify a given date in T-SQL

Question

I am writing some T-SQL that should provide a minimum date value for several null fields:

DECLARE @epoch DATETIME; set @epoch='1900-01-01'; select min = ISNULL(ValidFromDate,@epoch) 

Is the string "1900-01-01" always return the date and time of January 1, 1900 in any environment, or will the SQL server try to parse the string according to the rules of the local culture?

If this is not good enough, what is the recommended way to specify a specific date / time in T-SQL?

Answer 1

The best format for string dates is the standard ISO-8601 format .

For variables and DATETIME columns, this is either YYYYMMDD (for dates without time; without dashes!) Or YYYY-MM-DDTHH:MM:SS (date + time).

Contrary to popular belief, YYYY-MM-DD for DATETIME variables DATETIME NOT language- / date-independent! If you try this, the second CAST will result in an error:

 SET LANGUAGE us_english SELECT CAST('2011-07-20' AS DATETIME) SET LANGUAGE british SELECT CAST('2011-07-20' AS DATETIME) 

but it will work

 SET LANGUAGE british SELECT CAST('20110720' AS DATETIME) 

This is the best format because it is independent of the language and date settings in SQL Server.

For SQL Server 2008 and DATE columns (just a date - no time), the format can also be YYYY-MM-DD (with dashes), which is also suitable for all settings.

Why is there such a difference between DATE and DATETIME , I'm not DATETIME , as it is now!

See Tibor Karaszi excellent . The ultimate DateTime data type guide for more information and examples.