New Built-In functions in 2012

Microsoft SQL Server 2012 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

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');

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;

Today is 6th-December

PARSE

This function will parse the value and return the result. In case if it is not able to parse, it will throw an error. You can use this function to convert strings/datetime to datetime or numeric values. Please trust me this function has performance issues compared to CAST/CONVERT.

PARSE ( string_value AS data_type [ USING culture ] )

This function expects three parameters:

• String_value - The expression which needs to be parsed.
• Data_type - To which data type we are converting to.
• CULTUre - To which culture, i.e., language such as gb-en, us-en. This is an optional parameter.

Let us see some examples to see how it works.

SELECT PARSE('08-04-2012' AS datetime USING 'en-US') AS Date

select cast('08-04-2012' AS datetime) as Date

Now the output is

So many people wonder why we have to use Parse when it produces the same output as the CAST function.
Suppose if you are not using ‘en-US’ culture, you are working in Paris, and your server date settings are native to  ‘fr-FR’, and you display date in DD/MM/YYYY format, then what will happen if you use the CAST function?
See the below queries:

SELECT PARSE('08-04-2012' AS datetime USING 'fr-fr') AS Date

select cast('08-04-2012' AS datetime) as Date

Now the output will be:

So now you might understand the real use of Parse I guess. And this is not the only one.
In my database I save inserted date as varchar and in the format “14-Aug-2012” like this. Then how will you convert that into normal datetime? That’s where the Parse function comes into use.
Consider my below queries and see the outputs.

SELECT PARSE('14-Aug-2012' AS datetime USING 'en-us') AS Date

SELECT PARSE('August 14,2012' AS datetime USING 'en-us') AS Date

Isn’t it good?? Saves developer’s time.
We have seen for datetime, now what about numeric? Let us see another example. In many countries, in decimals, instead of ‘.’  comma ‘,’ is used, especially in European countries. 125.00 is the same as 125,00 in France.
So in the database, I am having a varchar column but saving values in decimals and have records like

125,00

134,00

456,00

Now we have to go for culture options in the Parse function.

select parse('125,00' as decimal using 'en-US')

select parse('125,00' as decimal USING 'fr-FR')

These queries will give me output as

So the main advantage of the Parse function is to parse the expression for different cultures.

TRY_PARSE

It is similar to the Parse function, the only difference is when it is not able to parse, it will return a NULL instead of throwing an error as the Parse function.

TRY_PARSE ( string_value AS data_type [ USING culture ] )

This function expects three parameters:

• String_value - The expression which needs to be parsed
• Data_type - To which data type we are converting to
• CULTUre - To which culture i.e language such as gb-en, us-en; this is an optional parameter

Let us see some examples to understand how it works.

--try_parse demo

SELECT PARSE('13-04-2012' AS datetime USING 'en-us') AS Date

SELECT try_PARSE('13-04-2012' AS datetime USING 'en-us') AS Date

The output will be

And if you see in the message tab,

Because when Parse function is not able to parse, it will throw an error. But try_parse just returns null.
Now let us see another example. What will happen if I try to parse an alphanumeric string to integer?

select parse('df23' as int using 'en-US')

select try_parse('df34' as int USING 'en-US')

The output will be:

And if you check in the messages tab,

So try_parse avoids throwing an exception and returns null if it is not able to parse.

TRY_CONVERT

This function is similar to the existing Convert function but the difference is whenever it is not able to convert, it will return null.

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

This function expects three parameters:

• Data_type - To which data type we are converting to.
• Expression - The value to be converted/cast.
• style - Integer parameter that specifies how the cast expression should be. This is an optional parameter.

Let us see some examples to understand how it works.

SELECT CONVERT(datetime, '8/13/2012', 103) AS date

SELECT try_CONVERT(datetime, '8/13/2012', 103) AS date

Note here I am passing month as 13, so the conversion will fail. The first statement will throw an error. But what will happen with the secondone ?
See the output below.

And in the messages tab,

Now one of my varchar column always holds an integer value. But by mistake I saved an alphabet character once. So whenever I try to convert to integer using Cast or Convert, it throws me an error. But I don’t want error. If there are no integer values, then it should return null.
See the below queries and the output.

select try_convert(int,'a')

select convert(int,'a') 

So in general try_convert is similar to try_parse that whenever it is not able to convert, it will return null.

DateTime Functions

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;

DateFromParts

This function returns a date for the specified year, month, and day.

DATEFROMPARTS ( YEAR,MONTH,DAY )

• Year - Year value in integer
• Month - Month value in integer, between 1 and 12
• Day - Day value in integer, between 1 and 31

Returns: Date
Let us see an example of how to use this function. Before MS SQL Server 2012, when we want to form date from year, month, and day, we used to do like this:

declare @year int=2012

declare @month int=4

declare @day int=8

SELECT Date=Convert(datetime,convert(varchar(10),@year)+'-'+convert(varchar(10),@day)+'-'+convert(varchar(10),@month),103)

And a few people used to do like this also:

declare @year int=2012

declare @month int=4

declare @day int=8

select dateadd(month,@month-1,dateadd(year,@year-1900,@day-1))

But with SQL Server 2012, the datefromparts function will make this simple.

declare @year int=2012

declare @month int=4

declare @day int=8

select date=DATEFROMPARTS(@year,@month,@day)

Remember it returns date and not datetime.

DateTimeFromParts

Remember the last line I said, the DateFromParts function will only return you the date. So what if I need to get a datetime value from year, month, day, and time as well? That’s where the DateTimeFromParts function comes into picture.
This function returns a datetime for the specified year, month, day, hour, minute, second, and precision.

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

• Year - year value in integer
• Month - month value in integer, between 1 and 12
• Day - day value in integer, between 1 and 31
• Hour - hour value in integer
• Minute - minute value in integer
• Seconds - seconds value in integer
• Milliseconds - milliseconds value in integer

Returns: DateTime
Consider the below query.

declare @year int=2012

declare @month int=4

declare @day int=8

declare @hour int=5

declare @minute int=35

declare @seconds int=34

declare @milliseconds int=567

select date=DATETIMEFROMPARTS(@year,@month,@day,@hour,@minute,

            @seconds,@milliseconds)

The output will be

What will happen if I pass only 6 parameters like this?

declare @year int=2012

declare @month int=4

declare @day int=8

declare @hour int=5

declare @minute int=35

declare @seconds int=34

declare @milliseconds int=567

select date=DATETIMEFROMPARTS(@year,@month,@day,@hour,@minute,@seconds)

it will throw an error..

OK, what will happen if I pass six parameters and the 7^th parameter as null?

declare @year int=2012

declare @month int=4

declare @day int=8

declare @hour int=5

declare @minute int=35

declare @seconds int=34

declare @milliseconds int=567

select date=DATETIMEFROMPARTS(@year,@month,@day,@hour,@minute,@seconds,null)

This will return null. So whenever if one or more parameters are null, then the result also will be null.

DateTime2FromParts

This is similar to the above function but the difference is here we can set precision for time part and this function returns DateTime2.

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

• year - year value in integer
• month - month value in integer, between 1 and 12
• day - day value in integer, between 1 and 31
• hour - hour value in integer
• minute - minute value in integer
• fractions - fractions value in integer
• precision - precision value in integer

Return: DateTime2
Consider this below query.

declare @year int=2012

declare @month int=4

declare @day int=8

declare @hour int=5

declare @minute int=35

declare @seconds int=34

select date=DATETIME2FROMPARTS(@year,@month,@day,@hour,@minute,@seconds,0,0)

Here I am setting the fraction and precision both to 0. So the output will be

In the above query I am just changing precision to 2. Let us see what happens:

declare @year int=2012

declare @month int=4

declare @day int=8

declare @hour int=5

declare @minute int=35

declare @seconds int=34

select date=DATETIME2FROMPARTS(@year,@month,@day,@hour,@minute,@seconds,0,2)

Now you might be able to see the difference and understand what precision does. Yes it decides the precision of the datetime2 value to be returned. Let us see some more queries for this.

declare @year int=2012

declare @month int=4

declare @day int=8

declare @hour int=5

declare @minute int=35

declare @seconds int=34

select date=DATETIME2FROMPARTS(@year,@month,@day,@hour,@minute,@seconds,50,7)

This will return

declare @year int=2012

declare @month int=4

declare @day int=8

declare @hour int=5

declare @minute int=35

declare @seconds int=34

select date=DATETIME2FROMPARTS(@year,@month,@day,@hour,@minute,@seconds,567,2)

In the above query I set fractions as 567 and precision as 2, guess what will happen? Yes, it will throw an error. Unless I give precision 3 or more, this will throw an error.

SmallDateTimeFromParts

This function returns a smalldatetime value for the specified year, month, day, hour, and minute.

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

• Year - year value in integer
• Month - month value in integer, between 1 and 12
• Day - day value in integer, between 1 and 31
• Hour - hour value in integer
• Minute - minute value in integer

Return: SmallDateTime.
Consider this below query.

declare @year int=2012

declare @month int=4

declare @day int=8

declare @hour int=5

declare @minute int=35

select date=SmallDatetimeFromparts(@year,@month,@day,@hour,@minute)

The output will be

DateTimeOffsetFromParts

This function returns a datetimeoffset value for the specified date and time. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, 

             seconds, fractions, hour_offset, minute_offset, precision )

• Year - year value in integer
• Month - month value in integer, between 1 and 12
• Day - day value in integer, between 1 and 31
• Hour - hour value in integer
• Minute - minute value in integer
• Seconds - seconds value in integer
• fractions - fractions value in integer
• Hour_offset - hour portion of the time zone offset in integer
• Minute_offset - minute portion of the time zone offset in integer
• Precision - precision value in integer

Return: DateTimeOffset.
The offset arguments are used to represent the time zone offset. If the offset arguments are omitted, then the time zone offset is assumed to be 00:00, that is, there is no time zone offset. If the offset arguments are specified, then both arguments must be present and both must be positive or negative.
Consider the below query,

declare @year int=2012

declare @month int=4

declare @day int=8

declare @hour int=5

declare @minute int=35

declare @seconds int=45

select date=DATETIMEOFFSETFROMPARTS(@year,@month,@day,@hour,@minute,@seconds,567,12,40,3)

The output is

TimeFromParts

This function returns a time value for the specified  hour, minute, seconds, fractions, and precision.

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

• Hour - hour value in integer
• Minute - minute value in integer
• Seconds - seconds value in integer
• fractions - fractions value in integer
• precision - precision value in integer

Return: Time.
Consider this below query.

declare @hour int=5

declare @minute int=35

declare @seconds int=45

select date=TIMEFROMPARTS(@hour,@minute,@seconds,567,3)

The output will be