From http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServer:

Connecting via the OLEDB adaptor seems to have it’s quirks. Another posibility is to connect via ODBC (tested with Rails 1.1.6)

e.g. for a trusted connection

   1  
   2  development:
   3    adapter: sqlserver
   4    mode: odbc
   5    dsn: Driver={SQL Server};Server=<your server>;Database=<your db>;Trusted_Connection=yes;

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

dates

   1  
   2  {d 'yyyy-mm-dd'}
   3  {d '2001-12-31'}

timestamps

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

times

   1  
   2  {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?