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

Question

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?

Answer 1

You may use two ways first for the desired date format without time (00:00:00)

whereas second with time:

SELECT FORMAT(CONVERT(DATETIME, '23/07/2009', 103),'dd-MM-yyyy')

OUTPUT
---------------
'23/07/2009'





SELECT CONVERT(DATETIME, '23/07/2009', 103)

OUTPUT
---------------
'23/07/2009 00:00:00'

Answer 2

I've got a similar situation where the date is being held as a string in a UK format with slashes on SQL 2012 (dd/mm/yyyy).

The only thing which works for me is CONVERT(DATE, mydate, 3), this converts the date to yyyy-mm-dd correctly.

I found this blog a lot more useful than any of the answers above, none of which worked on my server, sadly I don't have the time to work out why.

https://blog.quest.com/various-ways-to-use-the-sql-convert-date-function/

Answer 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

Answer 4

You can convert a string to a date easily by:

CAST(YourDate AS DATE)

Answer 5

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)

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

Try:

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

this is British/French standard.