henrik staun poulsen (10/27/2010)
It that the correct (international) way to hard-code dates?
I think I would have used '2010-01-31' or is it '20100131' to ensure that it will work around the globe?
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)
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis