[tsql] 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 ?

SELECT convert(varchar(10), '23/07/2009', 111)

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.

Try:

SELECT convert(datetime, '23/07/2009', 103)

this is British/French standard.

SELECT convert(datetime, '23/07/2009', 103)

You can convert a string to a date easily by:

CAST(YourDate AS DATE)