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.
[sourcecode language=’sql’]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′);[/sourcecode]
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.
[sourcecode language=’sql’]SET NOCOUNT ON;
SELECT TRY_CONVERT(datetime, ‘20120131’, 103);[/sourcecode]
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.
[sourcecode language=’sql’]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’);[/sourcecode]
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.
[sourcecode language=’sql’]SET NOCOUNT ON;
SELECT TRY_PARSE(‘This is a date’ AS datetime USING ‘en-GB’);[/sourcecode]
Just like TRY_CONVERT(), it’s useful for testing output, for example:
[sourcecode language=’sql’]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;[/sourcecode]
Returns

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

Next SQL Server 2012 post – CREATE SEQUENCE