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)
Advertisements

Posted on April 27, 2011, in TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: