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