New Built-In TSQL Functions in SQL Server 2012

By Arshad Ali

New Built-in Functions in SQL Server 2012

SQL Server 2012 (Code named SQL Server Denali) introduces 14 new built in functions in four different categories with one existing function (LOG) being modified to have an optional second base parameter:

Category	Function Name
Conversion functions	PARSE
	TRY_CONVERT
	TRY_PARSE
Date and Time functions	DATEFROMPARTS
	DATETIME2FROMPARTS
	DATETIMEFROMPARTS
	DATETIMEOFFSETFROMPARTS
	EOMONTH
	SMALLDATETIMEFROMPARTS
	TIMEFROMPARTS
Logical functions	CHOOSE
	IIF
String functions	CONCAT
	FORMAT

Conversion Functions

There are three functions introduced in this category for the casting and conversion of values.

PARSE

This function is used to convert a string value to the requested/specified data type; you can optionally include the culture in which the string has been formatted. This function should be used to convert a string to a number or date/time data type. For other types of conversion you should continue using CAST or CONVERT. The performance of this function is slightly impaired because it relies on .NET CLR.

SELECT PARSE('01/03/2012' AS datetime) AS [Conversion using PARSE]
GO
SELECT PARSE('Tuesday, 3 January 2012' AS datetime2 USING 'en-US') AS [Conversion using PARSE]
GO

TRY_CONVERT

In previous versions of SQL Server we used the CONVERT function to convert a value to another data type, it works fine as long as the passed value can be converted to requested data type but if the value cannot be converted an exception is thrown. IN SQL Server 2012 TRY_CONVERT can be used which returns a value converted to the specified data type if the conversion is successful or it returns NULL instead of throwing exception is the conversion cannot be performed.

SET DATEFORMAT dmy;
SELECT CONVERT(datetime2, '12/31/2011') AS [Conversion using TRY_CONVERT] ;
GO
SELECT TRY_CONVERT(datetime2, '12/31/2011') AS [Conversion using TRY_CONVERT] ;
GO
--throws exception if explicit conversion is not allowed
SELECT TRY_CONVERT(xml, 1) AS [Conversion using TRY_CONVERT] ;
GO

TRY_PARSE

The PARSE function converts the string value to numeric or date/time data type value when the conversion is successful or else it fails with an exception being thrown. This is where TRY_PARSE can be used which returns a value converted to the specified data type if the conversion is successful or else it returns NULL.

SELECT PARSE('01/03/2012' AS datetime) AS [Conversion using PARSE]
GO
SELECT PARSE('01/03/2012' AS float) AS [Conversion using PARSE]
GO
SELECT TRY_PARSE('01/03/2012' AS datetime) AS [Conversion using TRY_PARSE]
GO
SELECT TRY_PARSE('01/03/2012' AS float) AS [Conversion using TRY_PARSE]
GO

Date and Time Functions

There are seven new Date/Time functions introduced in SQL Server 2012. Siz of these functions work as constructors to construct and return a value of type either date, time, datetime, datetime with offset etc. These functions can be used in cases where your system interoperates with other systems which store or accept date information in multiple parts (such as the day, month and year as separate data). With SQL Server 2012, you can easily combine these date parts and store them as a single value. The new functions also ensure the value is valid and does not fall beyond accepted ranges. See the code snippet below for example of the usage of each function:

--Syntax : DATEFROMPARTS (year, month, day)
--This function will be used to combine multiple date parts(year, month, day) into a value of DATE data type
SELECT DATEFROMPARTS (2012, 01, 03)
--Syntax : DATETIME2FROMPARTS (year, month, day, hours, minutes, seconds, fractions, precision)
--This function will be used to combine multiple date and time parts(year, month, day, hours, minutes, seconds, fractions, precision) into a value of DATETIME2 data type
SELECT DATETIME2FROMPARTS (2012, 01, 03, 14, 49, 00, 00, 00)
--Syntax : DATETIMEFROMPARTS (year, month, day, hours, minutes, seconds, milliseconds)
--This function will be used to combine multiple date and time parts(year, month, day, hours, minutes, seconds, milliseconds) into a value of DATETIME data type
SELECT DATETIMEFROMPARTS (2012, 01, 03, 14, 49, 00, 00)
--Syntax : DATETIMEOFFSETFROMPARTS (year, month, day, hours, minutes, seconds, fractions, hour_offset, minute_offset, precision)
--This function will be used to combine multiple date and time parts with offset (year, month, day, hours, minutes, seconds, fractions, hour_offset, minute_offset, precision) into a value of DATETIMEOFFSET data type(along with offset)
SELECT DATETIMEOFFSETFROMPARTS (2012, 01, 03, 14, 49, 00, 00, 05, 30, 00)
--Syntax : SMALLDATETIMEFROMPARTS (year, month, day, hours, minutes)
--This function will be used to combine multiple date and time parts(year, month, day, hours, minutes) into a value of SMALLDATETIME data type
SELECT SMALLDATETIMEFROMPARTS (2012, 01, 03, 14, 49)
--Syntax : TIMEFROMPARTS (hours, minutes, seconds, fractions, precision)
--This function will be used to combine multiple time parts(hours, minutes, seconds, fractions, precision) into a value of TIME data type
SELECT TIMEFROMPARTS (14, 49, 00, 00, 00) 

The EOMONTH function is little different from the above functions and returns the last day of the month (end of month) of the passed/specified date. You can also pass a second optional parameter to add months and return a calcualted result.

SELECT EOMONTH(GETDATE()) AS [Last day of current month]
SELECT EOMONTH(GETDATE(), -1) AS [Last day of last month]
SELECT EOMONTH(GETDATE(), 1) AS [Last day of next month]

Logical Functions

There are two new functions in this category : CHOOSE and IIF.

CHOOSE

CHOOSE can be used to return a value from a list of values based on an index number (note that index numbers start at 1). This function takes at least 2 arguments – the first argument must be an INT and the second argument and onwards can be any data type.

SELECT CHOOSE (1, 'A', 'B', 'C', 'D', 'E' ) AS [Using CHOOSE]
SELECT CHOOSE (4, 'A', 'B', 'C', 'D', 'E' ) AS [Using CHOOSE]
--Returns NULL if the index value exceeds the bounds of the array of values passed
SELECT CHOOSE (6, 'A', 'B', 'C', 'D', 'E' ) AS [Using CHOOSE]

IIF

If you have prior experience working with other programming languages you may have already encountered the IIF conditional function which is a compact way of writing IF..THEN..ELSE clause which returns the value based on the condition evaluation. The first argument of this function is a condition, if the condition is evaluated to TRUE then the second expression is evaluated and returned, if the condition is evaluated to FALSE then the third expression is evaluated and returned.

DECLARE @a int = 4;
DECLARE @b int = 40;
SELECT IIF ( @a > @b, 'Larger', 'Smaller' ) AS [Using CHOOSE]
SELECT IIF (1=1, 5+6, 1+2 ) AS [Using CHOOSE]
SELECT IIF (1=2, 5+6, 1+2 ) AS [Using CHOOSE]

String Functions

There are two new functions in this category – CONCAT and FORMAT.

CONCAT

You can pass two or more string values to the CONCAT function the function combines them all into string. All parameters passed to it of types other than a string will be converted to string implicitly and a NULL value will be converted to empty string (zero length string). The data type of the return value depends on the data type of the arguments passed.

SELECT CONCAT ('Today ', 'is ', '3rd', '-', 'January') AS [Using CONCAT]
SELECT CONCAT ('Today ', 'is ', 3, 'rd', '-', 'January') AS [Using CONCAT]

FORMAT

This function formats and returns the value passed to it in the specified format pattern. It returns a value of type nvarchar in case of success or NULL in case if the format pattern or culture is not valid. The performance of this function is slightly impaired as it relies on .NET CLR.

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy' ) AS [Using FORMAT]
SELECT FORMAT(GETDATE(), 'dd-MMMM-yyyy') AS [Using FORMAT]
SELECT FORMAT(GETDATE(), 'dddd, MMMM dd, yyyy') AS [Using FORMAT]
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy', 'en-US' ) AS [Using FORMAT]

Conclusion

In this article I talked about 14 new built in functions in four different categories coming up with SQL Server 2012 and which are going to make database developers lifes easier. These new useful functions emulate the functionality what we generally have in any other programming languages now at T-SQL/database engine level.