Microsoft SQL Server 2012 introduces 14 new built-in functions. These new
functions are:
Conversion functions
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.
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:
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
B. Complex IIF example
‘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.
A. Using CONCAT
So many people wonder why we have to use
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
See the below queries:
So now you might understand the real use of
In my database I save inserted date as
Consider my below queries and see the outputs.
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
So the main advantage of the
And if you see in the message tab,
Because when
Now let us see another example. What will happen if I try to parse an alphanumeric string to integer?
And if you check in the messages tab,
So
See the output below.
And in the messages tab,
Now one of my
See the below queries and the output.
So in general
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:
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.
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:
And a few people used to do like this also:
But with SQL Server 2012, the
Remember it returns date and not datetime.
This function returns a datetime for the specified year, month, day, hour, minute, second, and precision.
Consider the below query.
What will happen if I pass only 6 parameters like this?
OK, what will happen if I pass six parameters and the 7th
parameter as
Consider this below query.
In the above query I am just changing precision to 2. Let us see what happens:
Now you might be able to see the difference and understand what precision does. Yes it decides the precision of the
Consider this below query.
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,
Consider this below query.
Conversion functions
- PARSE
- TRY_CONVERT
- TRY_PARSE
- DATEFROMPARTS
- DATETIME2FROMPARTS
- DATETIMEFROMPARTS
- DATETIMEOFFSETFROMPARTS
- EOMONTH
- SMALLDATETIMEFROMPARTS
- TIMEFROMPARTS
- CHOOSE
- IIF
- 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). |
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 toCAST/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.
SELECT PARSE('08-04-2012' AS datetime USING 'en-US') AS Date
select cast('08-04-2012' AS datetime) as DateNow 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 DateNow 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,00Now 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 theParse
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 parsedData_type- To which data type we are converting toCULTUre- To which culture i.e language such as gb-en, us-en; this is an optional parameter
--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 DateThe 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 existingConvert
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.
SELECT CONVERT(datetime, '8/13/2012', 103) AS date
SELECT try_CONVERT(datetime, '8/13/2012', 103) AS dateNote 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. EOMONTHThis 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 integerMonth- Month value in integer, between 1 and 12Day- Day value in integer, between 1 and 31
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, theDateFromParts
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 integerMonth- month value in integer, between 1 and 12Day- day value in integer, between 1 and 31Hour- hour value in integerMinute- minute value in integerSeconds- seconds value in integerMilliseconds- milliseconds value in integer
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..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 returnsDateTime2.DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
year- year value in integermonth- month value in integer, between 1 and 12day- day value in integer, between 1 and 31hour- hour value in integerminute- minute value in integerfractions- fractions value in integerprecision- precision value in integer
DateTime2Consider 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 asmalldatetime
value for the specified year, month, day, hour, and minute.SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
Year- year value in integerMonth- month value in integer, between 1 and 12Day- day value in integer, between 1 and 31Hour- hour value in integerMinute- minute value in integer
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 beDateTimeOffsetFromParts
This function returns adatetimeoffset
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 integerMonth- month value in integer, between 1 and 12Day- day value in integer, between 1 and 31Hour- hour value in integerMinute- minute value in integerSeconds- seconds value in integerfractions- fractions value in integerHour_offset- hour portion of the time zone offset in integerMinute_offset- minute portion of the time zone offset in integerPrecision- precision value in integer
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 integerMinute- minute value in integerSeconds- seconds value in integerfractions- fractions value in integerprecision- precision value in integer
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
