New Functions in SQL Server 2012

There are many new new functions added to SQL Server 2012.  In this article I have described all the functions listed below.

Logical Functions

1. CHOOSE (Transact-SQL)
2. IIF (Transact-SQL)

Conversion Functions

1. PARSE (Transact-SQL)
2. TRY_PARSE (Transact-SQL)
3. TRY_CONVERT (Transact-SQL)

Date and time Functions

1. DATEFROMPARTS Function
2. TIMEFROMPARTS Function
3. DATETIMEFROMPARTS Function
4. EMONTH Function .. Etc

String Functions

1. FORMAT (Transact-SQL)
2. CONCAT (Transact-SQL)

Analytic Functions

1. First_Value Function
2. Last_Value Function

So let's have a look at a practical example of all the new SQL Server functions. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

First now start with Logical Functions.

Logical Function

IIF() Function

The IIF function is used to check a condition. Suppose X>Y. In this condition a is the first expression and b is the second expression. If the first expression evaluates to TRUE then the first value is displayed, if not the second value is displayed.

Syntax

IIF ( boolean_expression, true_value, false_value )

Example

DECLARE @X INT;

SET @X=50;

DECLARE @Y INT;

SET @Y=60;

Select iif(@X>@Y, 50, 60) As IIFResult

In this example X=50 and Y=60; in other words the condition is false.  Select iif(@X>@Y, 50, 60) As IIFResult returns false value that is 60.

Output

Choose() Function

This function returns a value out of a list based on its index number. You can think of it as an array kind of thing. The Index number here starts from 1.

Syntax

CHOOSE ( index, value1, value2.... [, valueN ] )

CHOOSE() Function excepts two parameters,

Index: Index is an integer expression that represents an index into the list of the items. The list index always starts at 1. 

Value: List of values of any data type.

Facts related to the Choose Function

The following are some facts related to the Choose Function.

1. Item index starts from 1

DECLARE @ShowIndex INT;

SET @ShowIndex =5;

Select Choose(@ShowIndex, 'M','N','H','P','T','L','S','H') As ChooseResult 

 

In the preceding example we use index=5. It will start at 1. Choose() returns T as output since T is present at @Index location 5.

Output

2.  When passed a set of types to the function it returns the data type with the highest precedence; see:

DECLARE @ShowIndex INT;

SET @ShowIndex =5;

Select Choose(@ShowIndex ,35,42,12.6,14,15,18.7)  As CooseResult

In this example we use index=5. It will start at 1. Choose() returns 15.0 as output since 15 is present at @ShowIndex location 5 because in the item list, fractional numbers have higher precedence than integers.

3. If an index value exceeds the bound of the array it returns NULL

DECLARE @ShowIndex INT;

SET @ShowIndex =9;

Select Choose(@ShowIndex , 'M','N','H','P','T','L','S','H')  As CooseResult

In this example we use index=9. It will start at 1. Choose() returns Null as output because in the item list the index value exceeds the bounds of the array; the last Index=8.

Output

4. If the index value is negative then that exceeds the bounds of the array therefore it returns NULL; see:

DECLARE @ShowIndex INT;

SET @ShowIndex =-1;

Select Choose(@ShowIndex, 'M','N','H','P','T','L','S','H')  As CooseResult

In this example we use index= -1. It will start at 1. Choose() returns Null as output because in the item list the index value exceeds the bounds of the array.

Output

5. If the provided index value has a float data type other than int, then the value is implicitly converted to an integer; see:

DECLARE @ShowIndex  INT;

SET @ShowIndex =4.5;

Select Choose(@ShowIndex ,35,42,12.6,13,15,20) As CooseResult

In this example we use index= 4.5. It will start at 1.  If the specified index value has a float data type other than int, then the value is implicitly converted to an integer. It returns the 13.0 as output since 15 is present at @ShowIndex=4.5 which means index is 4.

Output

Conversion Functions

Parse Function

This function converts a string to Numeric and Date and Time formats. It will raise an error if translation isn't possible. You may still use CAST or CONVERT for general conversions. It depends on the presence of the CLR.

Syntax

To demonstrate this new conversion function the following defines the syntax:

 PARSE ( string_value AS data_type [ USING culture ] )

The Parse Function contains three parameters. The Culture part of the function is optional.

string_value: String value to parse into the Numeric and Date and Time format.

data_type: Returns data type, numeric or datetime type.

culture: Culture part of the function is optional. A language (English, Japanese, Spanish, Danish, French etc.) to be used by SQL Server to interpret data. A culture can be specified if needed; otherwise, the culture of the current session is used. Culture can be any of the .NET supported cultures, not being limited to those supported by SQL Server. 

For Example

In this example we see the parse function with Cast and Convert functions. Execute the following to convert a string value to datetime using CAST, CONVERT and PARSE functions:

SELECT CAST('6/08/2012' AS DATETIME2) AS  [CAST Function Result]  -- Using CAST Function

GO

SELECT CONVERT(DATETIME2, '06/08/2012') AS [CONVERT Function Result] --Using Convert Function

Go

SELECT PARSE('06/08/2012' AS Datetime2 USING 'en-US') AS [PARSE Function Result] -- Using Parse Function

GO

Now press F5 to execute those commands. The result the command produces is:

As you will see, only PARSE is able to convert the string value to datetime and the first two queries that are using CAST and CONVERT will fail that as in the following:

SELECT CAST('Monday, 06 august 2012' AS DATETIME2) AS  [CAST Function Result]  -- Using CAST Function

GO

SELECT CONVERT(DATETIME2, 'Monday, 06 august 2012') AS [CONVERT Function Result] --Using Convert Function

Go

SELECT PARSE('Monday, 06 august 2012' AS Datetime2  USING 'en-US') AS [PARSE Function Result] -- Using Parse Function

GO

Now press F5 to execute the preceding commands. The output will be as in the following:

Try_Parse Function

This function works similarly to the parse function except if the conversion is successful then it will return the value as the specified data type. Otherwise it will return a NULL value. 

Syntax

TRY_PARSE ( string_value AS data_type [ USING culture ] )

Example

Using Parse Function

SELECT Parse ('Sunday, 06 august 2012' AS Datetime2  USING 'en-US') AS [PARSE Function Result] -- Using Parse Function

OUTPUT

Now Using Try_Parse Function

SELECT Try_Parse ('Sunday, 06 august 2012' AS Datetime2  USING 'en-US') AS [Try_PARSE Function Result] -- Using Try_Parse Function
GO

Try_Convert Function

This is similar to the convert function except it returns null when the conversion fails. If the conversion cannot be completed because the data type of the expression is not allowed to be explicitly converted to the specified data type, an error will be thrown.

Syntax

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

Example

SELECT TRY_CONVERT(Datetime2, '06/08/2012') AS [Try_Convert Function Result] -- Using Try_Convert Function

In the preceding example the conversion cannot be completed because the data type of the expression is not allowed to be explicitly converted to the specified data type.

Date and time Functions

SQL Server provides many functions for getting date and time values from their parts. You can do that easily using the functions DATEFROMPARTS, TIMEFROMPARTS, DATETIMEFROMPARTS etc. in SQL Server 2012. Before SQL Server 2012, you need to do many conversions to get the desired results. In this article, you will see how to get date and time values from their parts in SQL Server 2008 conversion. 

In SQL Server 2008

To get date and time values from their parts in SQL Server 2008:

Declare @Year as int=2013

Declare @Month as int=02

Declare @Day as int=20

Select Convert(Date, Convert(varchar(4), @Year) + '-' + Convert(varchar(2), @Month) + '-' +  Convert(varchar(2), @Day))

Output

In SQL Server 2012

DATEFROMPARTS Function

The DATEFROMPARTS function returns a date value for the specified year, month, and day. The syntax of the DATEFROMPARTS built-in date function is as follows:

DATEFROMPARTS ( year, month, day )

All three parameters of the DATEFROMPARTS function are required.

year: Integer expression specifying a year.

 

month: Integer expression specifying a month, from 1 to 12.

day: Integer expression specifying a day.

Example

Declare @Year as int=2013

Declare @Month as int=02

Declare @Day as int=20

 

Select DATEFROMPARTS(@Year, @Month, @Day)

 

Output

2013-02-20

TIMEFROMPARTS Function

The TIMEFROMPARTS function returns time values for the specified time and with the specified precision. The syntax of the TIMEFROMPARTS built-in date function is as follows: 

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

If the arguments are invalid, then an error is raised. If any of the parameters are null, null is returned. 

Example

Declare @hour as int=58

Declare @minute as int=46

Declare @seconds as int=20

Declare @fractions as int=0

Declare @precision as int=0

 

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

Output

58:46:20.0000000

DATETIMEFROMPARTS Function

The DATETIMEFROMPARTS function returns a DateTime value for the specified date and time. The syntax of the DATETIMEFROMPARTS built-in date function is as follows: 

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

If the arguments are invalid, then an error is raised. If any of the parameters are null, null is returned. 

Example

Declare @Year as int=2013

Declare @Month as int=12

Declare @Day as int=20

Declare @hour as int=58

Declare @minute as int=46

Declare @seconds as int=0

Declare @milliseconds as int=0 

 

Select DATETIMEFROMPARTS (@Year, @Month, @Day, @hour , @minute , @seconds, @milliseconds)

 

Output

2013-12-20 58:59:46.0000000  

The Eomonth Function

The Eomonth function returns the last day of the month that contains the specified date.

Syntax

The syntax of the "Month" built-in date function is as follows :

MONTH ( startdate [,month_to_add ] )

Here,

The "startdate" parameter can be an expression specifying the date for which to return the last day of the month.

The "month_to_add" is optional.

Example

Select getdate()asCurrentDate

Go

SelectEomonth(getdate())asMonth

Go

SelectEomonth('09/12/2012',2)as Month

Go

SelectEomonth('09/12/2012')asMonth

 

Output

String Functions

Format Function

The Format() function is used to format how a field is to be displayed.

Format converts the first argument to a specified format and returns the string value.

Syntax

FORMAT(column_name,format)

where both the field are required.

This function formats the date time. This function is used in the server .NET Framework and CLR. This function will solve many formatting issues for developers.

Example

DECLARE @d DATETIME = '20/03/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS US_Result;

Output

20/03/2011

Concat Function

It's the same concatenate function that we use in Excel, it will concatenate two or more strings to make it a single string. It implicitly converts all arguments to string types. This function expects at least two parameters and a maximum of 254 parameters.

Syntax

CONCAT ( string_value1, string_value2 [, string_valueN ] )

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

Example

SELECT CONCAT('Rohatash', ' '- 'Kumar') AS [Using concate Function];

Output

Rohatash-Kumar

Analytic Functions

The First_Value and Last_Value are part of the analytic functions. The First_Value Function returns the first value in an ordered set of values, and Similarly Last_Value function returns the last value from an ordered set of values.

Creating a table in SQL Server

Now we create a table named employee.

Create table Employee

(

EmpID int,

EmpName varchar(30),

EmpSalary int

)

 

The following is the sample data for the employee Table.

 

 

First_Value Function

 

The First_Value function is a new analytic function in SQL Server. It returns the first value in an ordered set of values. Here, you will see some examples related to the First_Value function.

 

Syntax

The following is the SQL Analytic First_Value function syntax:

First_Value ( [scalar_expression )
OVER ([partition_by_clause] order_by_clause)

Scalar_expression: can be a column, subquery, or other expression that results in a single value.
OVER: Specify the order of the rows.
ORDER BY: Provide sort order for the records.
Partition By: Partition by clause is a optional part of First_Value function and if you don't use it all the records of the result-set will be considered as a part of single record group or a single partition.

Example

Let us see the following query:

Select *, First_value(EmpSalary) OVER (order BY EmpSalary ) as First_ValueResut From Employee 

Output

First_Value Function with Partition By Clause

The Partition by clause is an optional part of the First_Value function. By using the PARTITION BY clause with the FIRST_VALUE function we can divide the result set by name.

Example

Select *, Lead(EmpName) OVER (partition by EmpName ORDER BY EmpName DESC) AS Result  From Employee

 

Output

 

 

Last_Value Function

 

The Last_Value function is also a new analytic function in SQL Server. It returns the last value in an ordered set of values. Here, you will see some examples related to the Last_Value  function.

 

Syntax

The following is the SQL Analytic Last_Value function syntax:

Last_Value ( [scalar_expression )
OVER ([partition_by_clause] order_by_clause)

Scalar_expression: can be a column, subquery, or other expression that results in a single value.
OVER: Specify the order of the rows.
ORDER BY: Provide sort order for the records.
Partition By: Partition by clause is a optional part of Last_Value function and if you don't use it all the records of the result-set will be considered as a part of single record group or a single partition and then ranking functions are applied.

Example

Let us see the following query:

Select *, LAST_VALUE(EmpSalary) OVER(ORDER BY EmpSalary ) AS Last_Salary

FROM Employee

Output

Last_Value Function with Partition By Clause

The Partition by clause is an optional part of the Last_Value function. By using the PARTITION BY clause with the Last_Value function we can divide the result set by name.

Example

Select *, LAST_VALUE(EmpSalary) OVER(partition by EmpName ORDER BY EmpSalary ) AS Last_Salary

FROM Employee

 

Output