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

Question

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)

Try:

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

this is British/French standard.

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