Built in Functions in SQL Server 2012

March 21, 2016 Sarabpreet Singh Anand

Microsoft SQL Server 2012 introduced 14 new built in functions. These new functions 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

In addition to the 14 new functions, one existing function has been changed. The existing LOG function now has an optional second base parameter.

Obviously all of these functions are useful in one way or the other, but I’ll be explaining five very useful functions which I feel are common\useful to all the environments.

1. 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)

This can be easily understood with the help of an example:

DECLARE @date DATETIME;

SET @date = ’12/06/2010′;

SELECT EOMONTH (@date) AS Result;

–or

DECLARE @date VARCHAR(255);

SET @date = ’12/06/2010′;

SELECT EOMONTH (@date) AS Result;

Both of these queries will return the same output i.e.,

–> 2010-12-31 00:00:00.000

In order to find the last day of the month for any future\previous month you must use the second parameter. You can provide a positive or negative value to the second argument based on the requirements. The example below explains it in a better way.

DECLARE @date DATETIME;

SET @date = GETDATE();

SELECT EOMONTH ( @date ) as ThisMonth;

SELECT EOMONTH ( @date, 1 ) as NextMonth;

SELECT EOMONTH ( @date, -1 ) as LastMonth;

2. 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) This function takes at-least 2 arguments, where the first must be an INT and the second onwards can be varchar.

The following example returns the Second item from the list of values that are provided.

Select Choose (2, ‘January’, ‘February’, ‘March’);

You can further enhance this functionality and make it dynamic, just declare a variable and depending upon the logic set the value and pass the variable as the first parameter.

Here’s the example for this:

declare @a int

set @a =2

SELECT CHOOSE ( @a, ‘January’, ‘February’, ‘March’);

3. IIF

If you’ve ever done some programming in Java or VB you must’ve realized what is this? Yes this is the same Conditional function which will return the value based on the condition you specified as the first argument. It takes three parameters where the first declares the condition and rest two are the results you want it to return in case the condition comes out to be true or false respectively.

A. Simple IIF example

DECLARE @a int = 45;

DECLARE @b int = 40;

SELECT IIF (@a > @b, ‘TRUE’, ‘FALSE’) AS Result;

You can also put multiple conditions using ‘and’, ‘or’ keywords which will help you to evaluate the condition based on multiple things.

B. Complex IIF example

DECLARE @a int = 45;

DECLARE @b int = 40;

SELECT IIF (@a>@b and @b>30, ‘TRUE’, ‘FALSE’) AS Result;

‘And’ keyword specifies that both the conditions @a>@b and @b>30 should be satisfied in order to make it true, whereas if you replace ‘and’ with ‘or’ keyword then the condition will be accepted as true even if one of them fails.

4. 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. It accepts a minimum of 2 (at-least) Arguments and maximum of 254 Arguments.

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 isvarbinary(max) orvarchar(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 isnvarchar(<= 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 isnvarchar(max).

A. Using CONCAT

SELECT CONCAT (‘Today ‘, ‘is ‘, 6th, ‘-‘, ‘December’) AS Result;

5. TRY_CONVERT

Seems this Function is carrying try and catch block with itself. In earlier versions of SQL Server whenever the cast was not successful we were getting error messages but now no need to worry using this function will try to convert the data-type and return the value if it succeeds otherwise it will return null rather than that ugly error, but wait a minute there is a catch to it, if you are requesting this function to convert something which is explicitly not allowed this will definitely end-up giving an error.

A. TRY_CONVERT returns null

SELECT TRY_CONVERT(float,’test’)

You can decorate this with a custom message to make it more clear like:

SELECT

CASE WHEN TRY_CONVERT(float,’test’) IS NULL

THEN ‘Cast failed’

ELSE ‘Cast succeeded’

END AS Result;

B. TRY_CONVERT fails with an error

SELECT TRY_CONVERT(xml, 4);

The result of this statement is an error, because an integer cannot be cast into an xml data type.

Hope this post helped you, do leave comments.

Regards

Sarabpreet Anand