New Date Functions in SQL Server 2012

Introduction

SQL Server 2012 introduced the seven new date functions: DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, EOMONTH, SMALLDATETIMEFROMPARTS and TIMEFROMPARTS.

DATEFROMPARTS

The DATEFROMPART function returns a date value from a given year, month and day. If the parameter value(s) are not valid then it throws an error. If any argument is NULL then the result is also "NULL".

Syntax

DATEFROMPARTS ( year, month, day )

Argument / parameter

Year: Integer value for specifying a year.
Month: Integer value for specifying month (from 1 to 12).
Day: Integer value for specifying day.

Example

SELECT DATEFROMPART(2013,9,26)

--Output
-- 09/26/2013

DATETIMEFROMPARTS

The DATETIMEFROMPARTS returns a DATETIME value from a given date and time. If the parameter value(s) are not valid then it throws an error. If any argument is NULL then the result is also "NULL".

Syntax

DATETIMEFROMPARTS ( Year, Month, Day, Hour, Minute, Seconds)

Argument / parameter

Arguments: Year, Month and Day are the same as for the DATEFROMPARTS function.

Hour: Integer value for specifying hours.
Minute: Integer value for specifying minutes.
Seconds: Integer value for specifying seconds.

Example

SELECT DATETIMEFROMPARTS ( 2013, 9, 26, 9, 59, 59)

SELECT DATETIMEFROMPARTS ( 2013, 9, 26, 9, 59, 59)

--Output
-- 2013-09-26 09:59:59

DATETIME2FROMPARTS

DATETIME2FROMPARTS returns a DATETIME2 value from a given date and time with specified precision. If any argument is invalid then this function raises an error. If any argument is null then this function returns "NULL".

Syntax

DATETIME2FROMPARTS ( Year, Month, Day, Hour, Minute, Seconds, Fractions, Precision )

Argument / parameter

Arguments Year, Month, Day, Hour, Minute and Seconds are same as DATETIMEFROMPARTS

Fractions: Integer value for specifying fractions.
Precision: This is Integer value that specifies the precision of the datetime2 value to be returned.

Example

SELECT DATETIME2FROMPARTS ( 2013, 9, 26, 9, 59, 59, 0, 0 )

--Output
-- 2013-09-26 09:59:59.0000000

The fraction argument depends on precision argument. If the precision value is zero then fraction value must be zero, otherwise this function raised error. If precision argument is NULL then this function raised error. For example if precision value is 3 then fraction represent s a value in milliseconds.

SELECT DATETIME2FROMPARTS ( 2013, 9, 26, 9, 59, 59, 50, 2 )

--Output
-- 2013-09-26 09:59:59.50

DATETIMEOFFSETFROMPARTS

The DATETIMEOFFSETFROMPARTS function returns a DATETIMEOFFSET from given date and time with specified offsets and precision. This function raised error if any supplied argument is invalid. If any required argument is null then this function returns NULL.

Syntax

DATETIMEOFFSETFROMPARTS ( Year, Month, Day, Hour, Minute, Seconds, Fractions, Hour Offset, Minute Offset, Precision )

Argument / parameter

Arguments Year, Month, Day, Hour, Minute, Seconds, Fractions and Precision are same as DATETIME2FROMPARTS function.

Hour Offset: Integer value that specifies the hour portion of the time zone offset.
Minute Offset: Integer value that specifies the minute portion of the time zone offset.

Here the Offset arguments specify a time zone offset. If Offset values are not supplied then the time zone offset value is considered to be 00:00 (there is no time zone offset). If a Minute Offset is specified without a Hour Offset then this function raises an error and vice versa. The fraction argument depends on the precision argument.

Example

SELECT DATETIMEOFFSETFROMPARTS ( 2013, 09, 26, 15, 23, 23, 0, 12, 0, 5 )

--Output
-- 2013-09-26 00:00:00.00000 +00:00

 SELECT  DATETIMEOFFSETFROMPARTS ( 2013, 09, 26, 14, 50, 23, 4, 12, 30, 2 )

--Output
-- 2013-09-26 14:50:23.40 +12:30

EOMONTH

The EMONTH function returns the last day of the month that contains the definite date with an optional offset.

Syntax

EOMONTH ( Start Date, Month to add )

Argument / parameter

Start Date: Date value that specifies the date for which the last day of the month is required.
Month to add: This is an optional integer argument specifying the number of months to add to the start date. If this addition overflows the valid range of dates then this function raises an error.

Example

DECLARE @MyDate VARCHAR(255) = '09/1/2013';
SELECT EOMONTH ( @MyDate ) AS Date1,
EOMONTH ( @MyDate,1 ) AS Date2,
EOMONTH ( @MyDate,-1 ) AS Date3

--Output
--Date1 Date2 Date3
------------------- ---------------- -----------------
-- 2013-09-30 2013-09-301 2013-08-31

SMALLDATETIMEFROMPARTS

The SMALLDATETIMEFROMPARTS function returns a SMALLDATETIME value for a given date and time. If any arguments are invalid then this function raises an error. If any required argument is NULL then this function returns NULL.

Syntax

SMALLDATETIMEFROMPARTS (Year, Month, Day, Hour, Minute )

Argument / parameter

The Year, Month, Day, Hour and Minute arguments are the same as for the TIME2FROMPARTS function.

Example

SELECT SMALLDATETIMEFROMPARTS ( 2013, 09, 26, 15, 23)

--Output
-- 2013-09-26 15:23

TIMEFROMPARTS

The TIMEFROMPARTS function returns a time value for a ven time with a ecified precision. If any arguments are invalid then this function raises an error. If any required argument is NULL then this functions returns NULL. If the precision value is null then this function raises an error. The fractions value depends on the precision value.

Syntax

TIMEFROMPARTS ( Hour, Minute, Seconds, Fractions, Precision )

Argument / parameter

The Hour, Minute, Seconds, Fractions and Precision arguments are the same as for the DATETIME2FROMPARTS function.

Example:

SELECT TIMEFROMPARTS ( 15, 30, 0, 0, 0)

--Output
-- 15:30:00.0

SELECT TIMEFROMPARTS ( 15, 30, 0, 5, 2)

--Output
-- 15:30:00.50