SQL 2012 – CONVERT and PARSE

In addition to the existing CAST/CONVERT, new functions are available for data type conversion.

    TRY_CONVERT:

TRY_CONVERT will execute a CONVERT, but it will return NULL instead of raising an error if the conversion cannot be made.
select CONVERT(datetime, ‘Sunday, April 1, 2012 3:00 PM’)
— Msg 241, Level 16, State 1, Line 2
–Conversion failed when converting date and/or time from character string.

select TRY_CONVERT(datetime, ‘Sunday, April 1, 2012 3:00 PM’)
returns:
NULL

select TRY_CONVERT(datetime, ‘April 1, 2012 3:00 PM’)
returns:
2012-04-01 15:00:00.000

If the conversion between two data types isn’t explicitly allowed, an exception will be raised.
select TRY_CONVERT(image, 1)
returns:
Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to image is not allowed.

    PARSE:

PARSE – BOL

According to Books Online, parse should be used to convert from a string to datetime or numeric types. For other types of conversion, CONVERT is recommended.

select PARSE(‘Sunday, April 1, 2012 3:00 PM’ as datetime)
returns a datetime:
2012-04-01 15:00:00.000

    TRY_PARSE:

The same as parse, but will return a NULL if the conversion can’t be made.

select TRY_PARSE(‘tomorrow, Monday’ as datetime)
returns:
NULL

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

%d bloggers like this: