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
String 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.
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 P
arse
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
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
Let us see some examples to understand how it works.
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 integerMonth - Month value in integer, between 1 and 12Day - 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 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
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 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
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 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
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 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
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 integerMinute - minute value in integerSeconds - seconds value in integerfractions - fractions value in integerprecision - 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
