Possible Duplicate:
best way to convert and validate a date string
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
The Declare statement is used to create field and group definitions, where you can define relations between fields or functions. A set of field definitions can be used to automatically generate derived fields, which can be used as dimensions. Qlik sense variables in scripting. Filtering by a calculated measure involving multiple fields in Qlik Sense. Hot Network Questions What is this metallic object with teeth in mouth, screw to tighten and a handle? What is the use of declare with option -t.
If @myDate is of type
DATETIME2
, would your answer be different?Community♦
KashKash6,24144 gold badges2323 silver badges4545 bronze badges
marked as duplicate by StuartLC, j0k, Jim Garrison, soldier.moth, unkulunkuluAug 29 '12 at 8:10
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
3 Answers
Based on the ISO 8601 standard, the following 3 formats in the question are valid:
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.
KashKash6,24144 gold badges2323 silver badges4545 bronze badges
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♦
StuartLCStuartLC84.9k1515 gold badges147147 silver badges210210 bronze badges
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
akluthakluth6,86533 gold badges3030 silver badges4040 bronze badges