New built-in functions SQL Server 2012

Introduction

SQL Server 2012 introduces 14 new built-in functions. These functions ease the path of migration for database developers enabling them creating applications. However these functions will also be useful to experienced users of SQL Server.

These are:

• 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

PARSE()

The PARSE function can be used to convert any string value to a Numeric or to a Date and Time format. It's also possible to convert to a specific Country datetime format.

       
 SELECT PARSE('31/03/2012' AS datetime USING 'nl-NL') 
 

Resulting in :

TRY_CONVERT ()
The TRY_CONVERT function converts the pased value to a specified data type. If the conversion is successful then it will return the value as a specified data type. Otherwise it will return a NULL value. 

       
SELECT TRY_CONVERT(int,'text');
SELECT TRY_CONVERT(Varchar(10),'text');
SELECT TRY_CONVERT(xml,'<test>Hello</test>');
SELECT TRY_CONVERT(xml,'<test>Hello<test>');
 

Resulting in :

TRY_PARSE()
The TRY_PARSE function in SQL Server 2012 can be used to convert any string value to a Numeric or to a Date and Time. 

SELECT TRY_PARSE('2012-03-31 21:54:57.447' AS datetime) 
SELECT TRY_PARSE('asda' AS int) 
  

Resulting in :



Date and time functions

DATEFROMPARTS()
DATEFROMPARTS returns a date value with the date portion set to the specified year, month and day, and the time portion set to the default. If the arguments are not valid, then an error is raised. If required arguments are null, then null is returned.



       
SELECT DATEFROMPARTS (2012, 03, 31)
SELECT DATEFROMPARTS (2012, 03, 32)
       




Resulting in

and

Msg 289, Level 16, State 1, Line 2 Cannot construct data type date, some of the arguments have values which are not valid.

DATETIME2FROMPARTS()
Returns a datetime2 value for the specified date and time and with the specified precision




       
 SELECT DATETIME2FROMPARTS (2012, 03, 31, 16, 56, 00, 00, 00)
 

resulting in







DATETIMEFROMPARTS() This function determines a datevalue for the specified date and time.

       
 SELECT DATETIME2FROMPARTS (2012, 03, 31, 16, 56, 00, 00, 00)
 



resulting in :







DATETIMEOFFSETFROMPARTS()
Returns adatetimeoffset value for the specified date and time and with the specified offsets and precision.



       
SELECT DATETIMEOFFSETFROMPARTS (2012, 03, 31, 1, 12, 00, 00, 05, 30, 00)
 



resulting in





EOMONTH()
Returns the last day of the month that contains the specified date, with an optional offset.



       
 SELECT EOMONTH(getdate())
       



Resulting in







SMALLDATETIMEFROMPARTS()
Return a smalldatetime value for the specified date and time.



       
SELECT DATETIMEFROMPARTS (2012, 03, 31, 12, 13, 00, 00)
 



resulting in





TIMEFROMPARTS()
Returns a time value for the specified time and with the specified precision.



       
SELECT DATETIMEFROMPARTS (2012, 03, 31, 12, 13, 00, 00)  
 

resulting in






Logical functions

CHOOSE()
This function is very simple and it returns specified index from a list of values. If Index is numeric, it is converted to integer. On the other hand, if index is greater than the element in the list, it returns NULL.

       
SELECT CHOOSE(2, 'Dog', 'Cat', 'etc')
 




resulting in :







IIF()
IIF stands for Inline IF. IIF functions is very similar to CASE function, however you can only evaluate the expression for two values and no more.



       
SELECT IIF (2>1, 'TRUE', 'FALSE') 
SELECT IIF (2>1 OR NOT 1>2, 'TRUE', 'FALSE')  
 




resulting in




String functions

CONCAT()
The output is a string, as the result of the concatenation of two or more strings – if the input is another type than string a conversion will be done implicit.

       
SELECT CONCAT ('Today', ' it is ', getdate())
 



Resulting in:




FORMAT() 
The function FORMAT() accepts 3 parameters. The first parameter is the VALUE parameter where you pass the date value or numeric value. The second parameter is the.NET Framework format string. The format parameter is case sensitive. "D" doesn’t mean the same as "d".  The third parameter is the culture. This can be any culture supported by the .NET Framework.



       
SELECT FORMAT(GETDATE(), 'dd-MM-yy') 
 




resulting in




Conclusion

This blogpost is an overview of the new built-in functions of SQL SERVER 2012. My background is BI and datawarehousing and some functions are useful but it seems that the new built-in functions are more suitable for website and application support.

Greetz
Hennie