Code Focused
10 More New T-SQL Functions
Part 1 of this article introduced four new T-SQL functions available in SQL Server 2012. Part 2 has the rest.
The first part of this article detailed four new T-SQL functions available in SQL Server 2012. This article demonstrates 10 additional functions. Dig in!
1. DATETIME2FROMPARTS
Building on the DATETIMEFROMPARTS, DATETIME2FROMPARTS offers similar functionality, but yields more precise DateTime2 data type, containing fractions of a second to a specified precision. Naturally, this means the syntax for the function will accommodate additional parameters for fractions and precision. The syntax for this function is DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision).
It returns a datetime2 value for separate integer values of year, month, day, hour, minutes, seconds, fractions and precision. All values must be valid date/time values. The "Precision" parameter must be big enough to accommodate the "fractions" value specified. Please note the maximum precision is 7.
SELECT DATETIME2FROMPARTS (2012, 8, 18, 14, 23, 44, 50, 2) AS NewDateValue
NewDateValue
2012-08-18 14:23:44.50
The next example shows the results of using invalid values. The DATETIME2FROMPARTS function produced an error because the precision value (1) wasn't large enough to accommodate the number of digits in the fraction (e.g., 2 for "50"):
SELECT DATETIME2FROMPARTS (2012, 8, 18, 14, 23, 44, 50, 1) AS NewDateValue
Msg 289, Level 16, State 5, Line 1
Cannot construct data type datetime2, some of the arguments have values which are not valid.
2. DATETIMEFROMPARTS
DATETIMEFROMPARTS is similar to DATEFROMPARTS, except it returns a DateTime value, not just Date.
Syntax: DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds). It returns a fully initialized datetime value, as seen here. If required arguments are NULL, then a NULL is returned. However, if the arguments aren't valid, an error is raised similar to previous functions. This function is capable of being remoted to SQL Server 2012 servers and later. Unfortunately, it won't be remoted to servers that have a version below SQL Server 2012.
SELECT DATETIMEFROMPARTS (2012, 08, 18, 16, 01, 39, 0) AS NewDateValue
NewDateValue
2012-08-18 16:01:39.000
3. DATETIMEOFFSETFROMPARTS
The function DATETIMEOFFSETFROMPARTS returns a datetimeoffset value for separate integer values of year, month, day, hour, minutes, seconds, fractions, precision, and time offset. The syntax for this function is DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision). The offset arguments represent the time zone offset. The same rules for precision discussed earlier also apply for this function.
SELECT DATETIMEOFFSETFROMPARTS (2011, 8, 18, 14, 30, 00, 5, 12, 30, 1) AS NewDateValue
NewDateValue
2011-08-18 14:30:00.5 +12:30
If required arguments are NULL, a NULL is returned. However, if the "Precision" value is NULL or not adequate, an error is raised:
SELECT DATETIMEOFFSETFROMPARTS (2011, NULL, 18, 14, 30, 00, 5, 12, 30, 1) AS NewDateValue
NewDateValue
NULL
SELECT DATETIMEOFFSETFROMPARTS (2011, 8, 18, 14, 30, 00, 500, 12, 30, 1) AS NewDateValue
Msg 289, Level 16, State 6, Line 1
Cannot construct data type datetimeoffset, some of the arguments have values which are not valid.
4. EOMONTH
The next function is EOMONTH, which returns the End-Of-Month date for the month of specified date. The syntax of the function is EOMONTH (start_date [, month_to_add]). The second argument is an optional month_to_add. This is an integer expression specifying the number of months to add to start_date before calculating the End-Of-Month date. In other words, month_to_add is added to start_date, then the function returns the last day of the month for the resulting date.
If this addition overflows the valid range of dates, an error is raised. This example demonstrates the conventional usage of EOMONTH, with and without the month_to_add parameter.
SELECT EOMONTH ('08/18/2012') AS NewDateValue
NewDateValue
2012-08-31
SELECT EOMONTH ('08/18/2012', 5) AS NewDateValue
NewDateValue
2013-01-31
The next example, on the other hand, shows an unconventional use of month_to_add parameter. Although Microsoft states this is an integer pattern, you can use a non-integer -- but the results may surprise you. First, I try a negative decimal number. It doesn't return a NULL or an error, but goes back in time 15 months ago and produces the correct end-of-month date. This might lead you to believe the function simply rounds non-integer values, and the calculations are based on that value. Hold on to that thought!
The second portion of the example also shows a non-integer value, but it's less than one. Note the resulting date is August 31, 2012. It's as if zero months were added to the given date. Once again, hold on to your thought of how non-integer values are handled.
The third portion of the example shows 1.99 for the month_to_add parameter. If you assumed that non-integer values are rounded, you assumed incorrectly, like most people who have seen this previously. Instead, non-integer values are truncated and only the integer portions are used. No rounding is used in the calculation as most people would assume. This logic is used in all other date and time functions as well.
SELECT EOMONTH ('08/18/2012', -15.1) AS NewDateValue
NewDateValue
2011-05-31
SELECT EOMONTH ('08/18/2012', 0.49) AS NewDateValue
NewDateValue
2012-08-31
SELECT EOMONTH ('08/18/2012', 1.99) AS NewDateValue
NewDateValue
2012-09-30
5. SMALLDATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS is very similar to DATETIMEFROMPARTS, except it returns a SmallDateTime type. The syntax for the function is SMALLDATETIMEFROMPARTS (year, month, day, hour, minute). If any of the arguments are not valid, an error is thrown. If required arguments are NULL, then NULL is returned. This function is capable of being used remotely only on servers with SQL Server 2012 servers or later.
The example shows the conventional use of SMALLDATETIMEFROMPARTS. Note the resulting value contains both date and time. Since "seconds" isn't an allowed parameter, the answer simply uses "00" as the default value for seconds.
SELECT SMALLDATETIMEFROMPARTS (2012, 8, 18, 14, 30) AS NewDateValue
NewDateValue
2012-08-18 14:30:00
Now we can view the results of using invalid parameters, as seen in the next example. The first portion shows the resulting is NULL when any of the arguments are NULL. The second portion of the example shows an error resulting from attempting to use month 13.
SELECT SMALLDATETIMEFROMPARTS (2012, 8, NULL, 14, 30) AS NewDateValue
NewDateValue
NULL
SELECT SMALLDATETIMEFROMPARTS (2012, 13, 18, 14, 30) AS NewDateValue
Msg 289, Level 16, State 4, Line 1
Cannot construct data type smalldatetime, some of the arguments have values which are not valid.
6. TIMEFROMPARTS
The last function in the Date/time category is TIMEFROMPARTS. This function returns a fully initialized time value from a set of integer arguments. Note this function returns only a time value, not a date/time value. The syntax for this function is TIMEFROMPARTS (hour, minute, seconds, fractions, precision).
As discussed with the EOMONTH function, decimal numbers will not cause an error. Instead, the decimal portion will be truncated and the integer portion will be used for the calculations. This logic applies to other date and time functions as well. This is why the two statements in this example yield the same result.
SELECT TIMEFROMPARTS (14, 23, 44, 500, 3) AS NewDateValue
NewDateValue
14:23:44.500
SELECT TIMEFROMPARTS (14, 23, 44.612, 500, 3) AS NewDateValue
NewDateValue
14:23:44.500
If any of the arguments are invalid (e.g., hour>23, minute>59, etc.) then an error is raised. Also, if the precision argument is NULL or invalid (not between 1-7), an error is raised. However, if any of the other parameters are NULL, then a NULL is returned. The next example demonstrates the result of using an invalid precision parameter and a NULL parameter for minutes. These statements yield an error and a NULL, respectively:
SELECT TIMEFROMPARTS (14, 23, 44.612, 500, 0) AS NewDateValue
Msg 289, Level 16, State 2, Line 1
Cannot construct data type time, some of the arguments have values which are not valid.
SELECT TIMEFROMPARTS (14, 23, NULL, 500, 3) AS NewDateValue
NewDateValue
NULL
Logical Functions
The next grouping of new T-SQL commands is logical functions; they include two new commands, CHOOSE and IIF. These commands are used for logical operations to help control the operation of a T-SQL batch.
7. CHOOSE
The CHOOSE function returns the item at the specified index from a list of values. The syntax for this function is CHOOSE (index, val_1, val_2 [, val_n]), where the index value ranges from 1-254. Naturally, this means you can have up to 254 values, of different type. Please note that most indexes developers deal with are 0-based; this function, however is 1-based. This can be a common pitfall for developers.
The return type of this function will vary depending on the type selected by the index, as well other types in the list of values. CHOOSE returns the data type with the highest precedence from the set of types in the list of values. T-SQL has 30 different data types, as listed in Figure 1, in order of decreasing precedence.
Generally speaking, when an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.
|
| Figure 1. T-SQL data type precedence. |
The example shows a conventional example of the CHOOSE function. The 1-based index returns the third item in the list. Since all items are the same data type, no conversion or precedence takes place.
SELECT CHOOSE (3, 'Manager', 'Director', 'Developer', 'Tester') AS NewResult;
NewResult
Developer
The next example is a little more complicated. First, notice the items are of various data types. The index parameter points to the third item, which is a string. Before returning the final value and data type, the CHOOSE function must evaluate all data types in the list of items. Remember, CHOOSE returns the data type with the highest precedence from the set of types in the list of values -- float in this case. Therefore, it tries to convert the varchar value of ‘Director' to a float, which yields an error.
SELECT CHOOSE (3, 08/18/2012, 4.3233, 'Director', 'Developer', 'Tester') AS NewResult;
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
A similar situation applies in the next example. In this case, it attempts to convert the varchar value of ‘45' to a float (highest precedence data type among the list of items), yielding 45.000. Note the precision of the resulting value matches that of the float in the list.
SELECT CHOOSE (3, 'Director', 'Developer', '45', '08/18/2012', 42.325) AS NewResult;
NewResult
45.000
8. IIF
The other new logical function is IIF. The syntax for the IIF function is IIF (boolean_expression, true_value, false_value) . It returns one of two values, either true_value or false_value, depending on the evaluation of the Boolean expression. The data type returned has the highest precedence of these two data types (true_value, false_value).
The next example shows a conventional scenario for the IIF statement. First, it evaluates the expression passed as the first parameter. If the expression evaluates to true, it returns the value of the second parameter. If false, it returns the value of the third parameter. In cases where the data types of the second and third parameter don't match, the data type returned will have the highest precedence of the two.
DECLARE @a int = 45;
DECLARE @b int = 40;
SELECT IIF (@a > @b, 'A is greater', 'B is bigger') AS NewResult;
NewResult
A is greater
Although IIF is a new statement, it's translated by the T-SQL processor as a CASE statement. When IIF's used remotely on another server, it acts semantically equivalent to the CASE statement. Therefore, it will have many characteristics and behaviors as the CASE statement. One such characteristic is its ability to be nested. IIF and CASE statements can be nested to a maximum of 10 levels.
Here's a scenario where the IIF statement is nested to two levels. As you can tell, it gets a little difficult to read with the nesting; so, although possible, it's not always recommended.
DECLARE @a int = 45;
DECLARE @b int = 40;
SELECT IIF(@a > @b, IIF(@a > @b, 'B is bigger', 'A is greater'), 'B is bigger') AS NewResult;
NewResult
B is bigger
String Functions
The fourth and last category of new T-SQL functions is String Functions, containing two new functions. Like other categories discussed here, this category provides functions that carry over from C# and VB.Net. The two new string functions are CONCAT and FORMAT.
9. CONCAT
The CONCAT function returns a string that's the result of concatenating two or more string values. The syntax for this function is CONCAT (string_value1, string_value2 [, string_valueN]), requiring 2 – 254 arguments. If the number of parameters is outside this range, an error is raised. Arguments are implicitly converted to string types. However, NULL values are implicitly converted to an empty string.
If all the arguments are NULL, an empty string of type varchar(1) is returned.
This example demonstrates the capabilities of the CONCAT function. Notice the parameters are of various data types, but the result is a varchar combing all the values.
SELECT CONCAT ('I was born on ', 08, '/', '18/1970 ', 'weighing ', 7.53, ' Pounds') AS NewResult
NewResult
I was born on 8/18/1970 weighing 7.53 Pounds
The next example shows the result of using NULL parameters. Notice how the NULL is converted to an empty string, having no effect on the result.
SELECT CONCAT ('Begin>', NULL, NULL, '<End') AS NewResult
NewResult
Begin><End
10. FORMAT
The second string function is FORMAT. It returns a value formatted with the specified format. The syntax for this function is FORMAT (value, format [, culture]), with culture being an optional parameter. The format parameter must contain a valid .NET Framework format string. This function is similar to its counterpart in the .Net Framework, although with some differences. Composite formatting is not supported. More information on composite formatting is available here.
The final example shows the FORMAT statement being used with the culture parameter. Note how both statements are identical, except for the culture parameter. The function yields the same results as its counterpart in the .NET Framework, with the date formatted for the United States and Australia, respectively.
DECLARE @Date DATETIME = '08/18/2012';
SELECT FORMAT (@Date, 'd', 'en-US') AS AmericanDate;
AmericanDate
8/18/2012
DECLARE @Date DATETIME = '08/18/2012';
SELECT FORMAT (@Date, 'd', 'en-AU') AS AustralianDate;
AustralianDate
18/08/2012
As you can see, SQL Server 2012 introduces many new and interesting improvements. Many of these functions will be easy to understand for .NET developers since they're borrowed from the .NET Framework. With 14 new T-SQL functions (from both this article and the first part, you now have more power and functionality when writing T-SQL scripts.