Correct way of specifying a given date in T-SQL

Question

I am writing some T-SQL which needs to enforce a minimum date value onto some null fields:

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

Is the string '1900-01-01' always going to return a datetime of Jan 1 1900 in any environment or will SQL server try to parse the string according to local culture rules?

If that's not good enough, what is the recommended way of specifying a particular date/time in T-SQL?

Answer 1

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

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

Contrary to popular belief, YYYY-MM-DD for DATETIME variables is NOT language-/dateformat-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 this will work:

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

This is the best format since it's indepdendent of your language and dateformat settings in SQL Server.

For SQL Server 2008 and columns of type DATE (just date - no time), the format can also be YYYY-MM-DD (with the dashes) and that works for all settings, too.

Why there is such a difference between DATE and DATETIME is beyond me - that's just the way it is for now!

See Tibor Karaszi's excellent The Ultimate Guide to the DateTime data types for even more details and examples.