• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

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

February 5, 2012 by Andy Hayes Leave a Comment

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

 

Related Posts:

  • How to fix "conversion failed when converting date and/or time from character string"
    How to fix "conversion failed when converting date…
  • sql grouping sets
    Using SQL GROUPING SETS for Multiple GROUP BY…

Filed Under: All Articles, SQL Tips and Tricks Tagged With: sql server 2012, t-sql

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 Google+

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Categories

  • All Articles (84)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (21)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to fix “conversion failed when converting date and/or time from character string”

Recent Posts

  • How to fix “conversion failed when converting date and/or time from character string”
  • Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query
  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial

Search

Connect

  • Twitter
  • Facebook
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©