Due to international issues, there are many ways of representing a date in SQL code. I prefer to use odbc canonical.

dates

{d 'yyyy-mm-dd'}
{d '2001-12-31'}

timestamps

{ts 'yyyy-mm-dd hh:mm:ss'}
{ts '2001-12-31 00:00:00'}

times

{t 'hh:mm:ss'}

Don't let the ODBC in the name fool you, these work in Microsoft SQL Server, and through ODBC connections.
I've tested it in Query Analyzer, Stored Procedures, ADO code (both OLEDB and ODBC providers), ADO.Net code (both SQLClient and ODBC).


See Microsoft reference:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetdate_time_and_timestamp_literals.asp

It is a pity that there isn't some sort of SQL standard for date representation. Or maybe there is and I don't know it?