How to convert a "dd/mm/yyyy" string to datetime in SQL Server?
323,298
Solution 1
The last argument of CONVERT
seems to determine the format used for parsing. Consult MSDN docs for CONVERT.
111
- the one you are using is Japan yy/mm/dd
.
I guess the one you are looking for is 103
, that is dd/mm/yyyy
.
So you should try:
SELECT convert(datetime, '23/07/2009', 103)
Solution 2
Try:
SELECT convert(datetime, '23/07/2009', 103)
this is British/French standard.
Solution 3
SELECT COALESCE(TRY_CONVERT(datetime, Value, 111),
TRY_CONVERT(datetime, Value, 103), DATEADD(year,-1,GetDate()))
You could add additional date formats as the Coalesce
will go through each until it returns a successful Try_Convert
Comments
-
Sim over 3 years
I tried this
SELECT convert(datetime, '23/07/2009', 111)
but got this error
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
However
SELECT convert(datetime, '07/23/2009', 111)
is OK though
How to fix the 1st one ?