Convert from varchar formatted as 'dd/mm/yyyy hh:mm:ss' to DateTime

Question

I'm stuck with the following. How do I convert strings that are formatted like the following into DateTime type?

SELECT CONVERT(DATETIME,LEFT('26/03/2012 00:00:00',10))

Error on the above is as follows:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Answer 1

You can use the CONVERT function with style 103 (dd/mm/yyyy), see this link for further details.

SELECT CONVERT(DATETIME,'26/03/2012 00:00:00',103)

Answer 2

You can use SET DATEFORMAT:

SET DATEFORMAT dmy;

SELECT CAST('26/03/2012 00:00:00' AS DATETIME);

LiveDemo