New built-in functions in SQL Server 2012

Introduction:

Microsoft SQL Server 2012 Release Candidate 0 introduces 14 new built-in functions. These functions will make the migration path for information workers by equalling functionality that is found in the expression languages.

In this blog post, I will be covering common usage of these functions because they are very useful for SQL developers in one way or other.

These new functions are:

Conversion functions

PARSE (Transact-SQL)
TRY_PARSE (Transact-SQL)
TRY_CONVERT (Transact-SQL)

Date and time functions

DATEFROMPARTS (Transact-SQL)
DATETIMEFROMPARTS (Transact-SQL)
DATETIME2FROMPARTS (Transact-SQL)
SMALLDATETIMEFROMPARTS (Transact-SQL)
DATETIMEOFFSETFROMPARTS (Transact-SQL)
TIMEFROMPARTS (Transact-SQL)
EOMONTH (Transact-SQL)

Logical functions

CHOOSE (Transact-SQL)
IIF (Transact-SQL)

String functions

CONCAT (Transact-SQL)
FORMAT (Transact-SQL)

Conversion Functions:

1) PARSE (Transact-SQL) – Parse a value and returns the result of an expression, translated to specified data type. It will raise error if translation isn’t possible. This function relies on CLR. It will take some performance overhead. You can only use this function to convert strings to or from date time and numeric values. You may still use CAST or CONVERT for general type of conversions. PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR). This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

Syntax:

PARSE ( string_value AS data_type [ USING culture ] )

PARSE(), expects three parameters,

string_value – String value to parse into the specified data type.
data_type – Return data type, numeric or datetime type
culture – A language (English, Japanese, Spanish, Danish, French etc.) which will used by SQL Server to interpret data.

Example:

Execute the following to convert string value to date time using CAST, CONVERT and PARSE functions:

SELECT CAST('18/02/2012' AS datetime2) AS [Using CAST Function]
GO

SELECT CONVERT(datetime2, '18/02/2012') AS [Using CONVERT Function]
GO

SELECT PARSE('18/02/2012' AS datetime2 USING 'en-GB') AS [Using PARSE Function]
GO

These commands will execute successfully and will produce exactly same output as below:

Using CAST Function
---------------------------
2012-02-18 00:00:00.0000000

Using CONVERT Function
---------------------------
2012-02-18 00:00:00.0000000

Using PARSE Function
---------------------------
2012-02-18 00:00:00.0000000

Now execute the following queries to see the real use of PARSE():

SELECT CAST('Saturday, 18 February 2012' AS datetime2) AS [Using CAST Function]
GO

SELECT CONVERT(datetime2, 'Saturday, 18 February 2012') AS [Using CONVERT Function]
GO

SELECT PARSE('Saturday, 18 February 2012' AS datetime USING 'en-GB') AS [Using PARSE Function]
GO

As you can see that only PARSE will be able to convert the string value to date time and the first two queries that are using CAST and CONVERT will fail that is shown below:

Using CAST Function
---------------------------
Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

Using CONVERT Function
---------------------------
Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

Using PARSE Function
-----------------------
2012-02-18 00:00:00.000

2) TRY_PARSE (Transact-SQL) – TRY_PARSE() function is similar to PARSE() with only difference is that it returns NULL if the conversion is not possible.

Syntax:

TRY_PARSE ( string_value AS data_type [ USING culture ] )

TRY_PARSE(), expects three parameters,

string_value – String value to parse into the specified data type.
data_type – Return data type, numeric or datetime type
culture – A language (English, Japanese, Spanish, Danish, French etc.) which will used by SQL Server to interpret data.

Example:

Execute the following to convert numeric value to int:

SELECT PARSE('10.20' AS INT) AS [Using PARSE Function]
GO

SELECT TRY_PARSE('10.20' AS INT) AS [Using TRY_PARSE Function]
GO

Here is the result:

Using PARSE Function
--------------------
Msg 9819, Level 16, State 1, Line 4

Error converting string value '10.20' into data type int using culture ''.

Using TRY_PARSE Function
------------------------
NULL

Since 10.20 is not an integer value PARSE() fails, and TRY_PARSE() returns NULL.

3) TRY_CONVERT (Transact-SQL) – Checks whether conversion from one type to other is possible or not. It transforms the source data into the target type if the conversion is possible. Returns NULL if the conversion is not possible.

Syntax:

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

TRY_CONVERT(), expects three parameters,

data_type – The data type into which to cast expression.
expression – The value to be cast.
style – Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.

Example:

Execute the following:

SET DATEFORMAT DMY;
GO

SELECT CONVERT(datetime2, '02/18/2012', 103) AS [Using CONVERT Function];
GO

SELECT TRY_CONVERT(datetime2, '02/18/2012', 103) AS [Using TRY_CONVERT Function];
GO

Here is the result set:

Using CONVERT Function
---------------------------
Msg 241, Level 16, State 1, Line 2

Conversion failed when converting date and/or time from character string.

Using TRY_CONVERT Function
---------------------------
NULL

As you can see the both queries fail conversion but only first query that is using COVERT function throws error whereas query that is using TRY_CONVERT returns NULL indicating that conversion fails.

4) Date and time functions

4.1) DATEFROMPARTS – The DATEFROMPARTS function, returns a date value with the date part set to the specified year, specified month and the specified day, and the time portion set to the default.

Syntax:

DATEFROMPARTS ( year, month, day )

DATEFROMPARTS(), expects three parameters,

Year – Integer expression specifying a year.
Month – Integer expression specifying a month, from 1 to 12.
Day – Integer expression specifying a day.

Example:

Execute the following:

DECLARE  @YEAR INT = 2012
	,@MONTH INT = 2
	,@DAY INT = 18

SELECT DATEFROMPARTS(@YEAR, @MONTH, @DAY) AS [Using DATEFROMPARTS Function]
GO

Here is the result set:

Using DATEFROMPARTS Function
----------------------------
2012-02-18

4.2) DATETIMEFROMPARTS – The DATETIMEFROMPARTS function, returns full datetime value with the date time part set to the specified year, specified month, specified day, specified hour, specified minute, specified second and the specified milliseconds.

Syntax:

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

DATETIMEFROMPARTS(), expects seven parameters,

Year – Integer expression specifying a year.
Month – Integer expression specifying a month, from 1 to 12.
Day – Integer expression specifying a day.
Hour – Integer expression specifying hours.
Minute – Integer expression specifying minutes.
Seconds – Integer expression specifying seconds.
Milliseconds – Integer expression specifying milliseconds.

Example:

Execute the following:

DECLARE @YEAR INT = 2012
	,@MONTH INT = 2
	,@DAY INT = 18
	,@HOUR INT = 11
	,@MINUTE INT = 59
	,@SECONDS INT = 59
	,@MILLISECONDS INT = 0

SELECT DATETIMEFROMPARTS(@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, @MILLISECONDS) AS [Using DATETIMEFROMPARTS Function]
GO

Here is the result set:

Using DATETIMEFROMPARTS Function
--------------------------------
2012-02-18 11:59:59.000

4.3) DATETIME2FROMPARTS – The DATETIME2FROMPARTS function, returns full datetime2 value with the date time part set to the specified year, specified month, specified day, specified hour, specified minute, specified second and the specified precision.

Syntax:

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

DATETIME2FROMPARTS(), expects eight parameters,

Year – Integer expression specifying a year.
Month – Integer expression specifying a month, from 1 to 12.
Day – Integer expression specifying a day.
Hour – Integer expression specifying hours.
Minute – Integer expression specifying minutes.
Seconds – Integer expression specifying seconds.
Fractions – Integer expression specifying fractions.
Precision – Integer literal specifying the precision of the datetime2 value to be returned.

Example:

Execute the following:

DECLARE @YEAR INT = 2012
	,@MONTH INT = 2
	,@DAY INT = 18
	,@HOUR INT = 11
	,@MINUTE INT = 59
	,@SECONDS INT = 59

SELECT DATETIME2FROMPARTS(@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 3) AS [Using DATETIME2FROMPARTS Function]
GO

Here is the result set:

Using DATETIME2FROMPARTS Function
---------------------------------
2012-02-18 11:59:59.500

4.4) SMALLDATETIMEFROMPARTS – The SMALLDATETIMEFROMPARTS function, returns full datetime value with the date time part set to the specified year, specified month, specified day, specified hour and the specified minute.

Syntax:

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

SMALLDATETIMEFROMPARTS(), expects five parameters,

Year – Integer expression specifying a year.
Month – Integer expression specifying a month, from 1 to 12.
Day – Integer expression specifying a day.
Hour – Integer expression specifying hours.
Minute – Integer expression specifying minutes.

Example:

Execute the following:

DECLARE @YEAR INT = 2012
	,@MONTH INT = 2
	,@DAY INT = 18
	,@HOUR INT = 11
	,@MINUTE INT = 59

SELECT SMALLDATETIMEFROMPARTS(@YEAR, @MONTH, @DAY, @HOUR, @MINUTE) AS [Using SMALLDATETIMEFROMPARTS Function]
GO

Here is the result set:

Using SMALLDATETIMEFROMPARTS Function
-------------------------------------
2012-02-18 11:59:00

4.5) DATETIMEOFFSETFROMPARTS – The DATETIMEOFFSETFROMPARTS function returns a full datetimeoffset. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.

Syntax:

DATETIMEOFFSETFROMPARTS(year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)

DATETIMEOFFSETFROMPARTS(), expects ten parameters,

Year – Integer expression specifying a year.
Month – Integer expression specifying a month, from 1 to 12.
Day – Integer expression specifying a day.
Hour – Integer expression specifying hours.
Minute – Integer expression specifying minutes.
Seconds – Integer expression specifying seconds.
Fractions – Integer expression specifying fractions.
Hour_offset – Integer expression specifying the hour portion of the time zone offset.
Minute_offset – Integer expression specifying the minute portion of the time zone offset.
Precision – Integer literal specifying the precision of the datetimeoffset value to be returned.

Example:

Execute the following:

DECLARE @YEAR INT = 2012
,@MONTH INT = 2
,@DAY INT = 18
,@HOUR INT = 11
,@MINUTE INT = 59
,@SECONDS INT = 59

SELECT DATETIMEOFFSETFROMPARTS(@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 5, 30, 3) AS [Using DATETIMEOFFSETFROMPARTS Function]
GO

Here is the result set:

Using DATETIMEOFFSETFROMPARTS Function
--------------------------------------
2012-02-18 11:59:59.500 +05:30

4.6) TIMEFROMPARTS – The TIMEFROMPARTS function, returns a full time value as shown in the below query result. It is important to note that the fractions argument actually depends on the precision argument.

When fractions have a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second. When fractions have a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second. When fractions have a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

Syntax:

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

TIMEFROMPARTS(), expects five parameters,

Hour – Integer expression specifying hours.
Minute – Integer expression specifying minutes.
Seconds – Integer expression specifying seconds.
Fractions – Integer expression specifying fractions.
Precision – Integer literal specifying the precision of the time value to be returned.

Example:

Execute the following:

DECLARE @HOUR INT = 11
	,@MINUTE INT = 59
	,@SECONDS INT = 59

SELECT TIMEFROMPARTS(@HOUR, @MINUTE, @SECONDS, 500, 3) AS [Using TIMEFROMPARTS Function]
GO

Here is the result set:

Using TIMEFROMPARTS Function
----------------------------
11:59:59.500

4.7) EOMONTH – This function takes two parameters first being start_date which is mandatory and the second one is Month_to_add which is optional. This function will return the last day of the month (also termed as EOM or end of the month) for the given date, By passing the second argument also it will add the months to the start_date and then returns the last day of the month as per the final date (start_date + Month_to_add)

Syntax:

EOMONTH ( start_date [, month_to_add ] )

EOMONTH(), expects two parameters,

Start_date – Date expression specifying the date for which to return the last day of the month.
Month_to_add – Optional integer expression specifying the number of months to add to start_date.

Example:

Execute the following:

DECLARE @STARTDATE DATETIME = GETDATE()

SELECT EOMONTH (@STARTDATE, 1) AS [Using EOMONTH Function]
GO

Here is the result set:

Using EOMONTH Function
----------------------
2012-03-31

5) Logical Functions:

5.1) CHOOSE – This function can be used to return the value out of a list based on its index number (Note: Index no. here starts from 1).

Syntax:

CHOOSE ( index, val_1, val_2 [, val_n ] )

CHOOSE(), expects two parameters,

Index – Is an integer expression that represents a 1-based index into the list of the items following it.
Value – List of values of any data type.

Example:

Execute the following:

DECLARE @index INT

SET @index = 3

SELECT CHOOSE(@index, 'Cricket', 'Football', 'Hockey') AS [Using CHOOSE Function]

SET @index = 2

SELECT CHOOSE(@index, 'Cricket', 'Football', 'Hockey') AS [Using CHOOSE Function]

Here is the result set:

Using CHOOSE Function
---------------------
Hockey

Using CHOOSE Function
---------------------
Football

5.2) IIF – Conditional function which will return the value based on the condition you specified as the first argument. This is similar to SSRS IIF expression.

Syntax:

IIF ( boolean_expression, true_value, false_value )

IIF(), expects three parameters,

Boolean_expression – A valid Boolean expression.
True_value – Value to return if boolean_expression evaluates to true.
False_value – Value to return if boolean_expression evaluates to false.

Example:

Execute the following:

DECLARE @a INT = 45;
DECLARE @b INT = 40;

SELECT IIF(@a >;@b
		AND @b >;30, 'TRUE', 'FALSE') AS [Using IIF Function];

SET @a = 25

SELECT IIF(@a >;@b
		AND @b >;30, 'TRUE', 'FALSE') AS [Using IIF Function];

Here is the result set:

Using IIF Function
------------------
TRUE

Using IIF Function
------------------
FALSE

5.3) String Functions:

CONCAT – It’s the same concatenate function that we use in excel, it will concatenate two or more strings to make it single string. It implicitly converts all arguments to string types.

The return type depends on the type of the arguments. The following table illustrates the mapping:

Input type	Output type and length
If any argument is a SQL-CLR system type, a SQL-CLR UDT, or nvarchar(max)	nvarchar(max)
Otherwise, if any argument is varbinary(max) or varchar(max)	varchar(max) unless one of the parameters is an nvarchar of any length. If so, then the result is nvarchar(max).
Otherwise, if any argument is nvarchar (<;= 4000)	nvarchar(<;= 4000)
Otherwise, in all other cases	varchar(<;= 8000)unless one of the parameters is an nvarchar of any length. If so, then the result is nvarchar(max).

Syntax:

CONCAT ( string_value1, string_value2 [, string_valueN ] )

CONCAT(), expects at least two parameters and maximum of 254 parameters,

String_value – A string value to concatenate to the other values.

Example:

Execute the following:

SELECT CONCAT ( 'I'
		,' '
		,'Love'
		,' SQL Server'
		,' 2012 DENALI'
		) AS [Using CONCAT Function];

Here is the result set:

Using CONCAT Function
-----------------------------
I Love SQL Server 2012 DENALI

5.4) FORMAT – Function for locale-aware formatting of date/time and number values as strings.

Syntax:

FORMAT ( value, format [, culture ] )

FORMAT(), expects at three parameters,

Value – Expression of a supported data type to format.
Format – nvarchar format pattern.
Culture – Optional nvarchar argument specifying a culture.

“If the culture argument is not provided, then the language of the current session is used. This language is set either implicitly, or explicitly by using the SET LANGUAGE statement. culture accepts any culture supported by the .NET Framework as an argument; it is not limited to the languages explicitly supported by SQL Server . If the culture argument is not valid, FORMAT raises an error.”

Example:

Execute the following:

DECLARE @Date DATETIME = '2011/21/02';

SELECT FORMAT(@Date, 'yyyy/MM/dd hh:mm:ss tt', 'en-GB') AS [Using FORMAT Function];

SELECT FORMAT(@Date, 'D', 'en-US') AS [Using FORMAT Function];

Here is the result set:

Using FORMAT Function
-----------------------
2011/02/21 12:00:00 AM

Using FORMAT Function
-------------------------
Monday, February 21, 2011

Conclusion

In this article I have covered with examples the fourteen new built in functions in four different categories that are introduced with Microsoft SQL Server 2012 Release Candidate 0. These built-in functions makes database developers lifes easier by helping them to format data quickly. These new useful functions equal the functionality that is found in other expressions languages.

4 thoughts on “New built-in functions in SQL Server 2012”

dhlotter (@dhlotter) says:

May 28, 2012 at 6:46 am

Great post. good leg work.

LikeLike

Harsha says:

June 11, 2012 at 6:33 am

very useful information dude….thanks for sharing

LikeLike

Ruth2010! says:

August 10, 2012 at 5:41 am

The use of IIF also replace a lot of code, like IF and Case, consider this:

Declare @somestuff int
Declare @result nvarchar(10)

Select @somestuff=101

if @somestuff >=100
BEGIN
Select @result=’Over 100′
END
ELSE
BEGIN
Select @result=’Below 100′
END
select @result

–This does the same…
Select IIF(@somestuff>100,’Over 100′,’Below 100′) as nvarchar

LikeLike

sriharsha2410 says:

August 10, 2012 at 3:45 pm

But IIF() cannot be used inside a view.

LikeLike

Basit's SQL Server Tips

It is all about passion!

New built-in functions in SQL Server 2012

4 thoughts on “New built-in functions in SQL Server 2012”

Leave a comment Cancel reply

Rate this:

Share this:

4 thoughts on “New built-in functions in SQL Server 2012”

Leave a comment Cancel reply