DateTime Functions in SQL Server 2012

100

Article

DateTime Functions

DATEFROMPARTS (year, month, day)
DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)
DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
EOMONTH (start_date)

DateFromParts

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

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

Returns: Date

Let us see an example of how to use this function. Before Microsoft SQL Server 2012, when we want to format a date with year, month and day, we used to do that like this:

declare @year int=2014
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)

Result

But with SQL Server 2012, the datefromparts function will make this simple.

declare @year int=2014
declare @month int=4
declare @day int=8
select date=DATEFROMPARTS(@year,@month,@day)

Result

Note: 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 is useful.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 integer
Month: month value in integer, between 1 and 12
Day: day value in integer, between 1 and 31
Hour: hour value in integer
Minute: minute value in integer
Seconds: seconds value in integer
Milliseconds: milliseconds value in integer

Returns: DateTime

Consider the following query.

declare @year int=2014
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)

Result

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

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

year: year value in integer
month: month value in integer, between 1 and 12
day: day value in integer, between 1 and 31
hour: hour value in integer
minute: minute value in integer
fractions: fractions value in integer
precision: precision value in integer

Return: DateTime2

Consider this following query:

declare @year int=2014
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 preceding query I am just changing the precision to 2. Let us see what happens:

declare @year int=2014
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)

Result

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 integer
Month: month value in integer, between 1 and 12
Day: day value in integer, between 1 and 31
Hour: hour value in integer
Minute: minute value in integer

Return: SmallDateTime.

Consider this following query.

declare @year int=2014
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 integer
Month: month value in integer, between 1 and 12
Day: day value in integer, between 1 and 31
Hour: hour value in integer
Minute: minute value in integer
Seconds: seconds value in integer
fractions: fractions value in integer
Hour_offset: hour portion of the time zone offset in integer
Minute_offset: minute portion of the time zone offset in integer
Precision: 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 following query:

declare @year int=2014
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 integer
Minute: minute value in integer
Seconds: seconds value in integer
fractions: fractions value in integer
precision: precision value in 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 for datetime. You will understand why after I explain this. How will you calculate the last date for the current month in SQL Server 2008?

declare @date1 datetime=getdate()
select dateadd(month,datediff(month,-1, @date1),-1)

Result

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 calculated
Month_to_Add: number of months to add to the start_date; this is an optional parameter.

So EOMONTH will return the date, that is the last date of the month entered. I am writing another query for the preceding queries but using EOMONTH.

Select EOMONTH(getdate()) EnodOfthismonth

Result

In case you want to know the last date for last month or the month before that or next month, then we need to use the optional parameter Month_to_Add.