Hugo Kornelis (4/2/2010)
Here you specify a string constant, using the 'yyyy-mm-ddd' format. SQL Server will convert the string constant to a date. Although the 'yyyy-mm-dd' format is not guaranteed to be interpreted correctly in all cases (only 'yyyymmdd', 'yyyy-mm-ddThh:mm:ss' and 'yyyy-mm-ddThh:mm:ss.mmm' are guaranteed), I have not found cases where it is misinterpreted.
I believe the ODBC formats are guaranteed too, since they have a fixed format definition, do not depend on DATEFORMAT, and are multi-language.
On the downside, the ODBC formats are not an international standard, only return DATETIME (not any of the new 2008 types), and are non-deterministic. Not ideal, then 🙂
Examples:
SELECT { ts '2010-04-02 23:49:30.723' };
SELECT { d '2010-04-02' };
SELECT { t '23:49:30.723' };
Books Online:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi