How to convert a "dd/mm/yyyy" string to datetime in SQL Server?
TsqlStringDatetimeTsql Problem Overview
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 ?
Tsql Solutions
Solution 1 - Tsql
The last argument of CONVERT seems to determine the format used for parsing. Consult https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#date-and-time-styles">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 - Tsql
Try:
SELECT convert(datetime, '23/07/2009', 103)
this is British/French standard.
Solution 3 - Tsql
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
Solution 4 - Tsql
SELECT convert(varchar(10), '23/07/2009', 111)
Solution 5 - Tsql
SQL Server by default uses the mdy date format and so the below works:
SELECT convert(datetime, '07/23/2009', 111)
and this does not work:
SELECT convert(datetime, '23/07/2009', 111)
I myself have been struggling to come up with a single query that can handle both date formats: mdy and dmy.
However, you should be ok with the third date format - ymd.
Solution 6 - Tsql
SELECT convert(datetime, '23/07/2009', 103)
Solution 7 - Tsql
You can convert a string to a date easily by:
CAST(YourDate AS DATE)