New Built in Functions in SQL Server 2012 : DateTime Functions

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:

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 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

And the output will be: