SQL Server 2012: TRY_PARSE, PARSE and TRY_CONVERT in datetime format

Recently came across some new functions released in SQL Server 2012: PARSE, TRY_PARSE and TRY_CONVERT, I was very excited about these .Net like functions and immediately tried them. Something interesting happened during my test.

 

Cast, convert return expected result:

select cast(‘20120202’ as datetime) as date select convert(datetime,‘20120202’, 102)as date

Result:

date
———————–
2012-02-02 00:00:00.000
(1 row(s) affected)
date
———————–
2012-02-02 00:00:00.000
(1 row(s) affected)
 
I then tried below query:
select
try_parse(‘20120202’asdatetimeusing‘en-US’)asdate

select parse(‘20120202’asdatetimeusing‘en-US’)asdate

 
 

Surprisingly, I got this:

date ———————– NULL (1 row(s) affected) date ———————– Msg 9819, Level 16, State 1, Line 2 Error converting string value ‘20120202’ into data type datetime using culture ‘en-US’. PARSE and TRY_PARSE doesn’t like yyyymmdd format!

 

How about TRY_CONVERT?

 

I tried silimar query like this one:

select try_convert(datetime,‘20120202’,102)asdate

 

Luckly, I got exactly what I wanted:

date
———————–
2012-02-02 00:00:00.000
(1 row(s) affected)

Note to myself, PARSE and TRY_PARSE behave differently when they work on datetime conversion and might cause unexpected result. 

 
 
 

 

Advertisements

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