Hi Henrik,

A good point. Using '31-Jan-2010' in T-SQL to represent a date is not safe. If the server is set to a language other than English, you might get an error. For instance:

SET LANGUAGE French;

SELECT DATEADD(MONTH, 3, '31-Jan-2010');

Le paramètre de langue est passé à Français.

-----------------------

Msg 241, Level 16, State 1, Line 2

Échec de la conversion d'une valeur datetime à partir d'une chaîne de caractères.

(The text of these messages roughly translate to "language succesfully set to french" / "error converting character value to datetime")

For the datetime data type, The only formats that are guaranteed to work correctly in all circumstances are:

* yyyymmdd - for dates without time portion. No dashes. dots, slashes or other punctuation. The resulting datetime value will have its time portion set to midnight.

* yyyy-mm-ddThh:mm:ss - for dates with a time portion. Dashes between the date components, colons between the time components, and an uppercase T to seperate date from time.

* yyyy-mm-ddThh:mm:ss.mmm - as above, but with microseconds added. These will be rounded to the nearest 1/300 of a second.

For the new date data type, I think (too little hands-on SQL 2008 experience to be sure) that you can choose between these two formats:

* yyyymmdd - same as for datetime values, use when compatibility among date/time types is required

* yyyy-mm-dd - as presribed by ISO standards, but unfortunately not guaranteed for datetime; the new date data type finally allowed the SQL Server team to fix this (though unfortunately not for the old types - for backwards compatibility reasons, I guess)