New T-SQL features in SQL Server 2012 – Conversion Functions

SQL Server 2012 (“Denali”) is nearly here and with all new releases, there are always new features to know about.

I am very interested to find out more about the product and my next few posts will talk about some of the new T-SQL features of SQL Server 2012.

This first post will cover the new conversion functions in SQL Server 2012.

I am using the latest download from Microsoft – SQL Server 2012 RC0 for my research.

TRY_CONVERT()

Should the function fail then NULL is returned. The following all return the same output bar the last statement which fails and returns NULL.

SET NOCOUNT ON;
SELECT TRY_CONVERT(datetime, '2012/01/31');
SELECT TRY_CONVERT(datetime, '2012-01-31');
SELECT TRY_CONVERT(datetime, '20120131');
SELECT TRY_CONVERT(datetime, '31-12-2012');

Returns

-----------------------
2012-01-31 00:00:00.000

There is an optional integer expression called “style” which is like CONVERT and actually shares the same integer values for styling the output.

SET NOCOUNT ON;
SELECT TRY_CONVERT(datetime, '20120131', 103);

Returns

-----------------------
2012-01-31 00:00:00.000

PARSE()

Accepts a nvarchar input up to 4000 chars and attempts to evaluate the input based on the value and datatype specified for the parse. The following examples return the same output.

SET NOCOUNT ON;
SELECT PARSE('Tuesday, 31 January 2012, 21:00' AS datetime USING 'en-GB');
SELECT PARSE('Tuesday, 31 January 2012, 9PM' AS datetime USING 'en-GB');
SELECT PARSE('Tuesday 31 January 2012 9PM' AS datetime USING 'en-GB');
SELECT PARSE('Tuesday 31 Jan 2012 9PM' AS datetime USING 'en-GB');
SELECT PARSE('Tuesday 31 Jan 12 9PM' AS datetime USING 'en-GB');

Returns

-----------------------
2012-01-31 21:00:00.000

The part which reads USING ‘en-GB’ is the culture specified. If no culture is specified then the language of the current session gets used.

Should the parse fail, then an error is produced.

TRY_PARSE()

Just like TRY_CONVERT(), TRY_PARSE() will return NULL if the TRY fails to parse the input. So trying to parse a string as a date would fail and return NULL. Again, you can specify USING to try a specific culture. The following returns NULL.

SET NOCOUNT ON;
SELECT TRY_PARSE('This is a date' AS datetime USING 'en-GB');

Just like TRY_CONVERT(), it’s useful for testing output, for example:

SET NOCOUNT ON;
SELECT CASE WHEN TRY_PARSE('This is a date' AS datetime USING 'en-GB') IS NULL THEN
'Parse failed'
ELSE
'Parse succeeded'
END;

Returns

---------------
Parse failed

Next SQL Server 2012 post – CREATE SEQUENCE

 

It's only fair to share...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on PinterestBuffer this pageShare on RedditShare on StumbleUpon
About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on

Speak Your Mind

*


*