Correct way of setting datetime value to a variable in SQL Server [duplicate]

Question

Possible Duplicate:
best way to convert and validate a date string

While assigning a datetime value to a datetime variable in SQL Server, which format is the best to adopt for culture agnostic reasons and why?

The date intended below is 01-Dec-2013

DECLARE @myDate DATETIME
SET @myDate = '2013-12-01'
SET @myDate = '20131201'
SET @myDate = '12/01/2013'
SET @myDate = '2013-12-01T00:00:00.000'
SET @myDate = '2013-12-01T00:00:00'

If @myDate is of type DATETIME2, would your answer be different?

Answer 1

Based on the ISO 8601 standard, the following 3 formats in the question are valid:

DECLARE @myDate DATETIME
SET @myDate = '20131201'
SET @myDate = '2013-12-01T00:00:00.000'
SET @myDate = '2013-12-01T00:00:00'

The advantage in using the ISO 8601 format is that it is an international standard. Also, datetime values that are specified by using this format are unambiguous. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE settings.

Answer 2

This has been covered before e.g. best way to convert and validate a date string

ISO-8601 format is YYYYMMDD for just dates, or YYYY-MM-DDTHH:mm:ss for date with time

If you can't guarantee ISO format, then you should set the applicable DATEFORMAT beforehand

Edit

Re : Would you still use this for datetime2

Yes, in fact this is clearly stated in MSDN here. Because of the additional precision to 100ns, ISO 8601 format is YYYY-MM-DDTHH:mm:ss[.nnnnnnn]