SQL Server 2012 (formerly code-named Denali) CTP3 adds several new functions that will make our lives a little bit easier in several areas: conversion and parsing, date and time, logical, string manipulation and math. Some of the functions are objectively very important and convenient to have, whereas others are important because they enable easier migration from other platforms, e.g., Microsoft Access. I’ll describe the new functions according to their categories.
For sample data I used a database called TSQL2012.
Conversion and Parsing
New functions in this category: TRY_CONVERT, PARSE and TRY_PARSE.
The TRY_CONVERT function is one that many developers have been hoping to get for a long time. It works pretty much like the existing CONVERT function, only when the input value isn’t convertible, instead of generating an error, the function returns a NULL. Here’s an example, followed by its output:
SELECT TRY_CONVERT(INT, 100) AS try1, TRY_CONVERT(INT, 'abc') AS try2; try1 try2 ----------- ----------- 100 NULL
Like the CONVERT function, also TRY_CONVERT supports a third style argument where relevant.
One classic case where this function can be useful is in dynamic schema scenarios. Each row represents a single entity, attribute, value (EAV), where the values are stored as character strings. But even though the values are all stored in a character string column, each attribute conceptually can have a different type (number, data, etc.). You have the conceptual type of the value stored in its own column (call it thetype). Suppose you attempt to filter only attributes representing integers, convert to an INT type, and then do something with the result. So your filter looks something like this:
WHERE thetype = 'INT' AND CAST(val AS INT) > 10
I get into the details of why this form can actually fail in SQL Server in the second part of a two part series in my column titled T-SQL String Manipulation Tips and Techniques. For the purposes of this blog, suffice to say that this form can fail on a conversion error because SQL Server may actually attempt to handle the conversion before evaluating the left expression. With TRY_CONVERT you can easily avoid such failures using the following form:
WHERE thetype = 'INT' AND TRY_CONVERT(INT< val) > 10
Would be nice if SQL Server added in the future also a TRY_CAST function where the style isn’t relevant; perhaps even a more general TRY_THIS function that would work with any expression that generates a trappable error. For example, why not allow TRY_THIS(col1 / col2), and in case of an error like divide-by-zero simply return a NULL?
The PARSE function in essence does a conversion of an input string to the target type, but unlike CAST and CONVERT, it supports an optional USING clause indicating the culture. The culture is any valid culture supported by the .NET framework. If a culture isn’t specified, SQL Server will rely on the current session’s effective language. One of the benefits in this function when an explicit culture is used is that it allows you to phrase the values in a form that is based on your culture’s conventions, without worrying about the language of the user running your code.
Here’s an example parsing strings as dates, in one case using US English culture, and in the other, Japanese:
SELECT PARSE('7/17/2011' AS DATE USING 'en-US') AS dt1, PARSE('2011/7/17' AS DATE USING 'ja-JP') AS dt2;
dt1 dt2 ---------- ---------- 2011-07-17 2011-07-17
The TRY_PARSE to PARSE is like TRY_CONVERT is to CONVERT; namely, TRY_PARSE does the same as PARSE, only when the input isn’t converted to the target type, instead of generating an error, the function returns a NULL. For example, the following invocation of PARSE fails:
SELECT PARSE('7/17/11' AS DATE USING 'ja-JP') AS dt; Msg 9819, Level 16, State 1, Line 1 Error converting string value '7/17/11' into data type date using culture 'ja-JP'.
A similar attempt with TRY_PARSE returns a NULL:
SELECT TRY_PARSE('7/17/11' AS DATE USING 'ja-JP') AS dt; dt ---------- NULL
Date and Time
New functions in this category: EOMONTH, DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, SMALLDATETIMEFROMPARTS and TIMEFROMPARTS.
The EOMONTH function returns the end of month date corresponding to the input date and time value, with the time set to midnight, retaining the time zone if it exists. Here’s an example I ran on September 28th, 2011, invoking EOMONTH with SYSDATETIME as input:
SELECT EOMONTH(SYSDATETIME()) AS endofmonth; endofmonth ---------------------- 2011-09-30 00:00:00.00
If the input type is one of the supported date and time datatypes, the type of the output is that of the input. Otherwise, as long as the input is convertible to a date and time type, the type of the output is DATETIME2(7).
It’s important to note is that the function doesn’t return the last possible point in time for the respective month based on the type of the input, rather midnight of the last day of the month. As long as the values stored in the data have only the date, or use only midnight, it is actually quite convenient to use this function. As an example, the following query returns orders placed on the last day of the month:
SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE orderdate = EOMONTH(orderdate);
Here I’m relying on the fact that all order dates are stored with midnight in the time. If that’s not a guarantee, and the time can be other than midnight, to address the task correctly you would need to express the filter as a range, like so:
SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE orderdate >= EOMONTH(orderdate) AND orderdate < DATEADD(day, 1, EOMONTH(orderdate));
Surprisingly, SQL Server Denali CTP3 doesn’t also support functions for end of other periods (e.g., quarter, year), or the beginning of any period. For now, you have to roll your own. This reminds me of an amusing, though very practical, suggestion, by my friend and colleague Gianluca Hotz when he learned that there’s no support for other similar functions. He suggested creating a function called ENDOFTIME that will accept the period (e.g., MONTH, QUARTER, YEAR) as another input, and return the end of that period. Similarly a function called BEGINNINGOFTIME or STARTOFTIME could accept a value and a period, and return the beginning of that period.
For each date and time data type, SQL Server Denali provides a FROMPARTS function that allows constructing a value of this type from integer parts. This is useful in general, but also important for migrations from environments like Excel, Access and others that support such functionality.
Here’s sample code constructing a value of each of the date and time types:
SELECT DATEFROMPARTS(2012, 02, 12) AS DATE_FROMPARTS, DATETIME2FROMPARTS(2012, 02, 12, 13, 30, 5, 1, 7) AS DATETIME2_FROMPARTS, DATETIMEFROMPARTS(2012, 02, 12, 13, 30, 5, 997) AS DATETIME_FROMPARTS, DATETIMEOFFSETFROMPARTS(2012, 02, 12, 13, 30, 5, 1, -8, 0, 7) AS DATETIMEOFFSET_FROMPARTS, SMALLDATETIMEFROMPARTS(2012, 02, 12, 13, 30) AS SMALLDATETIME_FROMPARTS, TIMEFROMPARTS(13, 30, 5, 1, 7) AS TIME_FROMPARTS; DATE_FROMPARTS DATETIME2_FROMPARTS DATETIME_FROMPARTS -------------- ---------------------- ----------------------- 2012-02-12 2012-02-12 13:30:05.00 2012-02-12 13:30:05.997 DATETIMEOFFSET_FROMPARTS SMALLDATETIME_FROMPARTS TIME_FROMPARTS ---------------------------------- ----------------------- ---------------- 2012-02-12 13:30:05.0000001 -08:00 2012-02-12 13:30:00 13:30:05.0000001
DATE_FROMPARTS DATETIME2_FROMPARTS DATETIME_FROMPARTS
-------------- ---------------------- -----------------------
2012-02-12 2012-02-12 13:30:05.00 2012-02-12 13:30:05.997
DATETIMEOFFSET_FROMPARTS SMALLDATETIME_FROMPARTS TIME_FROMPARTS
---------------------------------- ----------------------- ----------------
2012-02-12 13:30:05.0000001 -08:00 2012-02-12 13:30:00 13:30:05.0000001
Functions in this category: CHOOSE and IIF.
The IIF and CHOOSE functions are another pair of functions that Denali supports to allow easier migration from environments running Access.
The CHOOSE function accepts an integer input followed by a list of values of any data type, and returns as output the value from the list in the position indicated by the first input. Here’s an example for using the function:
SELECT CHOOSE(1, 'a', 'b', 'c') AS first, CHOOSE(2, 'a', 'b', 'c') AS second; first second ----- ------ a b
Of course you can implement similar logic very easily using the standard CASE expression, but as mentioned, the point is making migrations from Access smoother.
The IIF function is another function Access supports. It accepts as first input a predicate, as second input an expression to return in case the predicate is true, and as third input an expression to return in case the input is false or unknown. Here’s an example for using the function:
SELECT IIF(1 = 2, 'a', 'b') AS iif_result; iif_result ---------- b
Just like with CHOOSE, it’s very simple to implement the IIF logic with a standard CASE expression, but the point was making Access migrations easier.
New functions in this category: CONCAT and FORMAT.
The CONCAT function concatenates the input values into a single result string. If you’re wondering what’s the reason for adding such a function when T-SQL already has a concatenation operator (+), there are two main reasons:
1. The concatenation operator + yields a NULL on NULL input. The CONCAT function converts NULL inputs to empty strings before concatenation. Of course you can get by using the COLAESCE function, replacing a NULL input with an empty string, but this makes the code messy.
2. Other platforms support the CONCAT function, and adding such support in SQL Server makes migration from those platforms easier.
As an example, consider the location attributes country, region and city, of customers. The region attribute simply isn’t applicable in some locations, in which case it is set to NULL. You want to generate a single string of all three location attributes, separating the applicable ones by commas. With the CONCAT function it’s very simple:
SELECT custid, city, region, country, CONCAT(city, ', ' + region, ', ' + country) AS location FROM Sales.Customers WHERE custid > 85;
custid city region country location ----------- --------------- --------------- --------------- --------------------- 86 Stuttgart NULL Germany Stuttgart, Germany 87 Oulu NULL Finland Oulu, Finland 88 Resende SP Brazil Resende, SP, Brazil 89 Seattle WA USA Seattle, WA, USA 90 Helsinki NULL Finland Helsinki, Finland 91 Warszawa NULL Poland Warszawa, Poland
Notice that when the region attribute was applicable it was made part of the result string, and when it wasn’t, the NULL was simply replaced with an empty string.
The FORMAT function allows you to format an input value to a character string based on a .NET format string. You can optionally indicate a culture when relevant. As an example, the following code formats the result of the GETDATE() function using the format string 'd' (meaning, short date pattern), in one case using US English culture, and in another Japanese:
SELECT FORMAT(GETDATE(), 'd', 'en-US') AS us, FORMAT(GETDATE(), 'd', 'ja-JP') AS jp
us jp ----------- ------------ 9/28/2011 2011/09/28
This function allows a lot of flexibility in formatting inputs. For example, the following code formats product IDs as 10-digit strings with leading zeros:
SELECT FORMAT(productid, '0000000000') AS strproductid, productname FROM Production.Products;
strproductid productname ------------- -------------- 0000000058 Product ACRVI 0000000009 Product AOZBW 0000000051 Product APITJ 0000000045 Product AQOKR 0000000033 Product ASTMN ...
Note, though, that the function relies on .NET for the purposes of formatting, which has overhead. Compared, for example, with functions like STR, the FORMAT function is much slower.
Enhanced function in this category: LOG.
So far SQL Server supported two functions that compute logarithms: LOG (for natural logarithm) and LOG10 (for logarithm with a base of 10). If you wanted to compute a logarithm with any other base, you had to do it mathematically, using a log with a supported base, dividing the log of the input value by the log of the input base. For example, to compute the logarithm of 256 using the base 2, you could use the following expression:
SELECT LOG(256) / LOG(2);
SQL Server Denali enhances the LOG function by supporting a second argument representing the base. So to achieve the same thing in Denali, you simply use the following expression:
SELECT LOG(256, 2);
Now life is good; of course, it could be made event better with support for TRY_THIS, ENDOFTIME and BEGINNINGOFTIME. :)