New Built in Functions in SQL Server 2012 : DateTime Functions

Santhosh Kumar Jayaraman
Sep 29, 2012
7.3k
0
1
Article

Microsoft SQL Server 2012 Release Candidate 0 has announced 7 new builtin DateTime functions.

Those new functions are:

DateFromParts
DateTimeFromParts
DateTime2FromParts
SmallDateTimeFromParts
DateTimeOffsetFromParts
TimeFromParts
EOMonth

DateFromParts

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

Syntax: 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 and31

Returns - Date

Let us see an example how to use this function. Before Microsoft SQL Server 2012, when we want to form a date from year, month and day, we had to do it 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 would 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, as in:

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 in the last line I said that the DateFromParts function will only return a 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 is useful.

This function returns a datetime for the specified year, month, day, hour, minute, second and precision.

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

Year: Year value as integer
Month: Month value as integer, between 1 and 12
Day: day value as integer, between 1 and 31
Hour: hour value as integer
Minute: minute value asn integer
Seconds: seconds value as integer
Milliseconds: milliseconds value as integer

Returns - DateTime

Consider the following 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 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 6 parameters and the 7th parameter as null?

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

DateTime2FromParts

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

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

Year: year value as integer
Month: month value as integer, between 1 and 12
Day: day value as integer, between 1 and 31
Hour: hour value as integer
Minute: minute value as integer
fractions: fractions value as integer
Precision: precision value as integer

Return - DateTime2

Consider the following 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 both the fraction and precision to 0. So the output will be:

In the preceding query I am just changing the 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 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:

In the preceding query I set the fractions to 567 and the precision to 2; guess what will happen. Yes it will throw an error. Unless I give precision 3 or more this will throw error.

SmallDateTimeFromParts

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

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

Year: year value as integer
Month: month value as integer, between 1 and 12
Day: day value as integer, between 1 and 31
Hour: hour value as integer
Minute: minute value as integer

Return - SmallDateTime

Consider the following 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 timezone offset value hour and minutes.

Syntax: DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

Year: year value as integer
Month: month value as integer, between 1 and 12
Day: day value as integer, between 1 and 31
Hour: hour value as integer
Minute: minute value as integer
Seconds: seconds value as integer
Fractions: fractions value as integer
Hour_offset: hour portion of the timezone offset as integer
Minute_offset: minute portion of the timezone offset as integer
Precision: precision value as integer

Return - DateTimeOffset

The offset arguments are used to represent the timezone offset. If the offset arguments are omitted, then the timezone offset is assumed to be 00:00; that is, there is no timezone offset. If the offset arguments are specified, then both arguments must be present and both must be positive or negative.

Consider the following 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.

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

Hour: hour value as integer
Minute: minute value as integer
Seconds: seconds value as integer
Fractions: fractions value as integer
Precision: precision value as integer

Return - Time

Consider this following 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 to datetime. You will learn why after I explain.

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 people would write it like 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.

Syntax: EOMONTH ( start_date [, month_to_add ] )

Start_Date: date for which an end date for the month is to be calculated
Month_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 specified.

I am writing another query for the preceding queries but using EOMONTH.

Select EOMONTH(getdate())

The output will be:

If I want to know the last date for the last month or the month before that or the next month, then we have to use the optional parameter Month_to_Add, as in:

Select EOMONTH(getdate(),-1) as lastmonth
Select EOMONTH(getdate(),-2) as monthbeforethat
select EOMONTH(getdate(),1) as nextmonth