SQL Server: Working With Date but With Format of Your Own Choice

Today, a blog reader asked that, why he is facing error for following simple varchar to datetime conversion query

SELECT CAST('24/04/2011' AS DATETIME)

Msg 242, Level 16, State 3, Line 1

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

But same query works fine, once he changes date and month position in given text.

SELECT CAST('04/24/2011' AS DATETIME)

By default SQL Server expects date in MDY or it works fine with MYD and YMD. And if your date format is other then above three formats, then you have to inform SQL Server about your date format by using SET statement.

SET DATEFORMAT dmy

SELECT CAST('24/04/2011' AS DATETIME)