Introduction
Microsoft SQL Server 2012 Release Candidate 0 has announced 3 new inbuilt
conversion and 7 datetime functions. In this article, let us see how to
use those new functions with their syntax and sample queries.
Those new functions are listed below.
- Conversion functions
Parse
Try_Parse
Try_convert
- DateTime Functions
DateFromParts
DateTimeFromParts
DateTime2FromParts
SmallDateTimeFromParts
DateTimeOffsetFromParts
TimeFromParts
EOMonth
Conversion Functions
There are three new conversion functions and for those who have already worked in
the .NET Framework, they will easily figure out what will the functions do when I say parse, tryparse.
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 Pa
rse 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
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 7th 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
EOMonth
If you ask me, I
will say that this is one of the important functions in the new version of SQL Server related with datetime. You will come to know why after I explain this.
How will you calculate the last date for the
current month in SQL Server 2008?
If you ask me I will write,
declare @date1 datetime=getdate()
select dateadd(month,datediff(month,-1, @date1),-1)
Also a few peoples write
this,
declare @date varchar(10)
set @date=convert(varchar,year(getdate()))+ '-' +convert(varchar,(month(getdate())+1))+'-01'
select dateadd(day,-1,@date)
Both will give the same output.
Now in SQL Server 2012, a new method is introduced to make this simple which is
EOMONTH
.
EOMONTH ( start_date [, month_to_add ] )
Start_Date
- the date for which end date for the month to
be calculatedMonth_to_Add
- number of months to add to the start_date
;
this is an optional parameter.
So EOMONTH
will return the date which is
the last date of the month entered.
I am writing another query for the above
queries but using EOMONTH
.
Select EOMONTH(getdate())
The output will be
In case you want to know the last date
for last month or the month before that or next month, then we have to use
the optional parameter Month_to_Add
.
Select EOMONTH(getdate(),-1) as lastmonth
Select EOMONTH(getdate(),-2) as monthbeforethat
select EOMONTH(getdate(),1) as nextmonth
and the output will be
I graduated as Production Engineer and started my career as Software Developer then worked as tester for a while before moving into Windows application development using Microsoft Technologies. But for the last few years i am working on javascript, React, Node, AWS, Azure Chatbots