Wednesday, February 11, 2009

TIP: create SQL server datetime using international date format (ISO 8061)

CAST(datepart(year, getdate()) AS varchar) + '-' +
RIGHT('0' + CAST(datepart(month, getdate()) AS varchar), 2) + '-' +
RIGHT('0' + CAST(10 AS varchar), 2) +
AS datetime)

Note that SQL Server 2005 does *NOT* accept all ISO 8061 date/time formats. This one works well, and the advantage of using an international date/time format is that the date string is not ambiguous.

The above SQL creates a string consisting of at least 2 digits for day and month, and ends up looking like this for example: '2009-02-10T00:00:00'

SQL accepts that format and can CAST it to datetime.