Code Focused

4 New T-SQL Functions

The latest version of SQL Server expands its capabilities. Here are four new things developers can do with it.

• By Sam Nasr
• 01/02/2013

SQL Server 2012 was released last April, making it the 11th major release of the product. Every new version has delivered new functionality that should excite developers, and this release is no different. With the latest release of SQL Server, Microsoft produced 14 new T-SQL functions. These functions can be separated into four different categories, as seen in Table 1.

These functions will be demonstrated using the "SELECT" statement, so no specific database structure is needed. They can be executed against any database, in a stored procedure or other scripts, just like all other T-SQL statements. Here, I'll discuss conversion functions and the first date and time function. An online companion article will detail the other date and time functions and discuss logical and string functions.

1. PARSE
PARSE is a T-SQL function that transforms a string expression into a date/time or number type. It accepts two parameters and an optional third parameter for culture. The syntax for the statement is PARSE (string_value AS data_type [USING culture]). Here's a typical usage scenario:

select Parse ('03042011' as float) AS NewResult
NewResult
3042011

Use caution when using the culture parameter. In the following example, both statements are identical, except for the culture:

select Parse ('08-18-2011' as datetime2 using 'en-US')  AS NewResult
NewResult
2011-08-18 00:00:00.0000000

select Parse ('08-18-2011' as datetime2 using 'en-AU')  AS NewResult
Msg 9819, Level 16, State 1, Line 1

Error converting string value '08-18-2011' into data type datetime2 using culture 'en-AU'.

Using 08-18-2011 is valid for the U.S. culture, but the same date will produce an error if rendered in an Australian format, because Australian culture requires a date to have the day followed by the month. "08" is a valid day, but "18" isn't a valid month, causing the error.

Using PARSE requires the CLR. This means a user can't remote in to another SQL Server machine without having the CLR installed and executing the PARSE command. This function is used only for converting strings to date/time or numeric types. It's highly suggested by the Microsoft team to use CAST or CONVERT for other type conversions.

The transform patterns are limited by styles listed in the CLR in System.Globalization.NumberStyles and DateTimeStyles enumerations, dictating the characters allowed and different patterns for the end result. For more information on transform patterns, see "NumberStyles Enumeration" in the MSDN Library.

2. TRY_CONVERT
The TRY_CONVERT function converts an expression to a specified data type. The syntax format is TRY_CONVERT (data_type [(length) ], expression [, style]). It's similar to PARSE, but with a few differences. First, it allows conversion to any data type. Second, it will return a NULL if not successful. However, it will return an error when the cast is explicitly not permitted. Last, it doesn't allow for the use of culture, but it does have an optional third parameter for Style. An example of this can be seen in the following code snippet, which converts a string containing date information into a datetime2 data type:

select TRY_CONVERT(datetime2, '08/18/2012') as NewResult
NewResult
2012-08-18 00:00:00.0000000

The following code shows a scenario when TRY_CONVERT will return a NULL:

select TRY_CONVERT(datetime2, '99/99/9999') as NewResult

NewResult
NULL

As seen in the first code sample, a string can be converted to a datetime2 type. However, if the string expression contains an invalid date, TRY_CONVERT will return a NULL.

The TRY_CONVERT function also allows for use of styles as an optional third parameter. Style differs from culture because it shows how the end result of the date will be displayed, not just the alignment of month and day. You can see this in Listing 1. For more information on Styles, see "CAST and CONVERT (Transact-SQL)" in the MSDN Library.

3. TRY_PARSE
Next on the list of new T-SQL functions is TRY_PARSE. The function syntax is TRY_PARSE (string_value AS data_type [USING culture]). This is similar to the PARSE function, except that it returns NULL if the value isn't a valid representation of the data type, where PARSE Function raises an error. Traditionally, any function name in the Microsoft .NET Framework starting with "Try_" usually indicates it will return a NULL, not an error, if the expression can't be evaluated. The following code shows a typical TRY_PARSE usage scenario:

SELECT TRY_PARSE('08/18/2012' AS datetime2 USING 'en-US') AS NewResult
NewResult
2012-08-18 00:00:00.0000000

Here, the difference of TRY_PARSE can be seen (I used the same parameters used for PARSE, but got different results):

select TRY_PARSE ('08-18-2011' as datetime2 using 'en-US') AS NewResult
NewResult
2011-08-18 00:00:00.0000000

select TRY_PARSE ('08-18-2011' as datetime2 using 'en-AU') AS NewResult
NewResult
NULL

The first statement in here yields the same results as its counterpart in the first PARSE sample. However, the second statement yields a NULL, not an error, because the string expression isn't a valid Australian date.

4. DATEFROMPARTS
The next category of the new T-SQL functions contains date and time functions. There are seven new date/time functions.

The first of these is DATEFROMPARTS. This function is used to return a date value for separate integer values of year, month and day. The function's syntax is DATEFROMPARTS (year, month, day).

DATEFROMPARTS is capable of being remoted only to SQL Server 2012 or later. Unlike other functions previously mentioned, all parameters are required, and it doesn't accept a style or culture parameter. All values must be valid date values, or it will return an error. If a NULL value is passed for any of the parameters, it will return NULL.

The following code shows correct usage of the DATEFROMPARTS function (note how it returns the date type):

SELECT DATEFROMPARTS (2012, 8, 18) AS NewDateValue
NewDateValue
2012-08-18

When invalid parameters are used, the results look like this:

SELECT DATEFROMPARTS (NULL, 8, 18) AS NewDateValue
NewDateValue
NULL

SELECT DATEFROMPARTS (1985, 13, 26) AS NewDateValue
Msg 289, Level 16, State 1, Line 1
Cannot construct data type date, some of the arguments have values which are not valid.

Note that DATEFROMPARTS returns NULL when any or all of the parameters are NULL. However, it returns an error if any of the date fields are invalid (for instance, month=13).

• Get Code Download