New Built-in Functions in SQL Server 2012 Denali

Who is online?  799 guests and 0 members
home  »  articles  »  New Built-in Functions in SQL Server 2012 Denali

New Built-in Functions in SQL Server 2012 Denali

change text size: A A A
Published: 12/6/2011 2:42:20 AM by  Sarabpreet Anand  - Views:  [16454]

Microsoft SQL Server 2012 Release Candidate 0 (RC 0) introduces 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 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).

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;
GO


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.


If you liked the posts, do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks

 

Regards
Sarabpreet Singh
@Sarab_SQLGeek
@SQLServerGeeks

tags : Denali, CHOOSE, CONCAT, EOMONTH, functions, IIF, New functions, SQL Server 2012, TRY_CONVERT
  To rate this article please  register  or  login

Author

Sarabpreet Anand Sarabpreet Anand (Member since: 3/15/2011 5:38:06 AM)
SQLServer-MVP, Vice President - SQLServerGeeks.com

Sarabpreet is SQLServer MVP, DBA, Speaker, Trainer, Blogger and Community Lead. You can find him speaking at a local UG Event or a SQL Webcast. He has 8+ years of Experience and worked with Industry Leaders like Wipro, HP and HCL. He has many SQL Certifications under his belt. His core competency lies in administration of SQL Server. Always ready to help, online \offline. His life’s mantra is “Knowledge Increases by sharing so, Pass it on”. To know about his speaking engagements visit: here...

Follow Sarab on  Twitter@Sarab_SQLGeek ,  Facebook ,  LinkedIn

Comments (10)

AmitK
12/6/2011 6:05:07 AM Amit Karkhanis said:

Hey Sarab..., Very organised, informative and to the point... I have already kept myself tuned to this Denali Series...

by
sarab
12/6/2011 5:17:11 PM Sarabpreet Anand said:

Thanks Amit, Glad you liked it. Smile

by
user146939
12/15/2011 9:19:06 AM Rashmi Patankar said:

Good one...but I am really not understanding the usefulness of CHOOSE function :-(

by
sarab
12/15/2011 12:29:18 PM Sarabpreet Anand said:

Hi Rashmi,

Good you also liked it.

Choose is just equavalent to Case but it makes it easy to code since all of the values are being placed in a single row or two. You can also say that its a filter on a list based on their index value.

Do let me know if you need further clarity on this.

Happy Learning.

by
user146939
12/16/2011 3:33:08 AM Rashmi Patankar said:

Thats correct but it's kinda hardcoding. Is there any way of making the list of values dynamic?

by
sarab
12/16/2011 8:32:44 PM Sarabpreet Anand said:

Hi,

At the moment all my tries are in Vain to make it dynamicUndecided... but i'm not saying its not possible

just give me sometime,  will try to find a way....

by
Ajay
2/2/2012 12:17:38 PM Ajay said:

Hi,

Why you have left the following function to explain.

  • DATEFROMPARTS
  • DATETIME2FROMPARTS
  • DATETIMEFROMPARTS
  • DATETIMEOFFSETFROMPARTS
  • SMALLDATETIMEFROMPARTS
  • TIMEFROMPARTS

Please provide us the same.

by
sarab
2/2/2012 6:20:41 PM Sarabpreet Anand said:

Hi Ajay,

Thanks for liking this, will try to break all those shackles of Laziness and complete these too Wink

 

by
Adesh
2/2/2012 6:31:01 PM Adesh said:

Thanx Sarab... You are providing some knowledge matter to the studnts like us.. :)

by
Pawan Chand
11/29/2012 3:30:17 PM Pawan Chand said:

One more method is introduced in SQL Server 2012 ie;  QUOTENAME

by

Leave a comment

Name:
  *  
Email:  (your email is kept secure)
 *  
Website:

example: "http://www.SQLServerGeeks.com"

 
Type the characters:
 *
 
   

SQLServerGeeks FaceBook group

SQLServerGeeks Events

Training on Microsoft Products & Technologies

Email Subscriptions

   Get the Most Recent Blogs in your inbox
Articles RSS Feed

Most Recent Articles