T-SQL tricks for SQL Server 2012: The joy of data values

Editor’s note: This is the first article in an occasional series about Transact-SQL changes in SQL Server 2012.

Whenever Microsoft releases a new version of SQL Server, the new version inevitably contains enhancements to Transact-SQL (T-SQL),

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at [email protected].

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in July 2012

as is the case with SQL Server 2012. The latest updates to T-SQL include numerous new and modified elements that extend the language’s capabilities. Many of these enhancements can be broadly separated into two categories -- those specific to individual data values and those that affect the result set as a whole.

In this tip series, we’ll examine a number of new T-SQL elements in both categories and review examples that demonstrate how each one works. Keep in mind, however, that this discussion is meant only to provide an overview of the various language enhancements; it’s not a detailed discussion of syntax rules.

For more on T-SQL for SQL Server

Read T-SQL subquery basics

Manage and query SQL Azure federations using T-SQL

Working with data values
SQL Server 2012 includes a number of changes to T-SQL that affect the way you can parse, convert, concatenate and generate data values. Plus, T-SQL now includes functions that let you test your conversions in order to better control statement logic, in the event a conversion should fail. The new language features also provide ways to build date and time values from individual integers. So let’s look at how several of those language elements work.

PARSE and TRY_PARSE
The PARSE function converts a string value to a specified type and in a format that maps to a .NET Framework culture, such as Dutch, Polish, Korean or Thai. If no culture is specified, SQL Server uses the language associated with the current session.

For example, in the following SELECT statement, the PARSE function converts a string value to the DATE data type and formats the results specific to the Czech culture:

DECLARE @date1 VARCHAR(8);
SET @date1 = CONVERT(VARCHAR(8), GETDATE(), 22); 
SELECT PARSE(@date1 AS DATE USING 'Cs-CZ');

The function converts the string value in the @date1 variable, which is based on the current date that’s retrieved through the GETDATE() function. The AS DATE keywords specify that the string be converted into the DATE data type. Using'Cs-Cz' elements indicates that the result be formatted based on the culture represented by the Cs-Cz code, which is Czech.

Because PARSE is used in this way, the SELECT statement returns the value 2012-04-06, for June 4, 2012. However, you can specify any available culture, which would affect the results. For instance, the following example uses the U.S. English culture code (en-US):

DECLARE @date2 VARCHAR(8);
SET @date2 = CONVERT(VARCHAR(8), GETDATE(), 22);
SELECT PARSE(@date2 AS DATE USING 'en-US');

Now the SELECT statement returns the date as 2012-06-04. Notice that the month and day are reversed from the results returned by the preceding example.

In some cases, a conversion will fail and SQL Server will return an error. For instance, the following example attempts to convert the string value today to the DATE data type:

DECLARE @date3 VARCHAR(8);
SET @date3 = 'today';
SELECT PARSE(@date3 AS DATE USING 'Cs-CZ');

Because you cannot convert this string to the DATE data type, the SELECT statement generates an error. But T-SQL now supports the TRY_PARSE function, which, as the name suggests, lets you test a conversion. In the following example, the SELECT statement uses TRY_PARSE to convert today to the DATE data type:

DECLARE @date4 VARCHAR(8);
SET @date4 = 'today';
SELECT TRY_PARSE(@date4 AS DATE USING 'Cs-CZ');

Instead of returning an error, the SELECT statement now returns a NULL value. You can use this logic to control the flow of your statement by setting up a test condition that verifies whether the conversion returns a NULL value before actually trying to make that conversion.

Note, however, that the TRY_PARSE function works just like the PARSE function if the conversion is successful. The following example uses TRY_PARSE to convert an actual date value, rather than a string value like today:

DECLARE @date5 VARCHAR(8);
SET @date5 = CONVERT(VARCHAR(8), GETDATE(), 22);
SELECT TRY_PARSE(@date5 AS DATE USING 'Cs-CZ');

As you would expect, the SELECT statement returns the value 2012-04-06, just as you saw with the PARSE function.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top