Search code examples
t-sqlstringdatetime

How to convert a "dd/mm/yyyy" string to datetime in SQL Server?

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?

Solution

  • 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)