13

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?

Community
  • 1
  • 1
Kash
  • 8,799
  • 4
  • 29
  • 48

3 Answers3

15

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.

Kash
  • 8,799
  • 4
  • 29
  • 48
  • does not work when `(SELECT r.date_format FROM master.sys.dm_exec_requests r WHERE r.session_id = @@SPID)` returns **`dmy`** – myro Apr 23 '21 at 09:48
3

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]

Community
  • 1
  • 1
StuartLC
  • 104,537
  • 17
  • 209
  • 285
-2

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

SET @myDate = '2013-12-01T00:00:00'

SET @myDate = '2013-12-01'

Those are the correct formats according to ISO 8601

akluth
  • 8,393
  • 5
  • 38
  • 42
  • 7
    Actually - if your variable is of `DATETIME` and you want to set the date only - the really proper format is `SET @myDate = '20131201'` - your last assignment is **not safe** under European language settings - it will fail.... – marc_s Aug 28 '12 at 16:14