SQL 2012 - Newly Introduced Date and Time Functions

• Download SQL source - 368 B 

Introduction 

As a SQL developer, we are always keen to find and understand new features that Microsoft releases with each new version, may be SQL or any other Microsoft packages. The functions mentioned in the below articles are some of the new T-SQL Date and Time functions introduced in SQL 2012. This should help in reducing time in writing some of complicated date time queries in a simple manner.

Using the Code  

DATETIME2FROMPARTS

Building on the DATETIME2FROMPARTS offers more precise DateTime2 data type. It contains fractions of a second to a specified precision. This means the syntax for the function will accommodate additional parameters for fractions and precision. The syntax for this function is DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision).

SELECT DATETIME2FROMPARTS (2014, 02, 18, 14, 23, 44, 50,  2) AS MyDateValue

MyDateValue
2014-02-18 14:23:44.50

DATETIMEFROMPARTS

DATETIMEFROMPARTS is similar to DATEFROMPARTS, except it returns a DateTime value, and not just Date. Syntax: DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds). It returns a fully initialized datetime value, as seen below. If required arguments are NULL, then a NULL is returned. However, if the arguments aren't valid, an error is raised similar to previous functions. This function is capable of being remoted to SQL Server 2012 servers and later.

SELECT DATETIMEFROMPARTS (2014, 02, 18, 16, 01, 39, 0) AS MyDateValue

MyDateValue
2014-02-18 16:01:39.000

DATETIMEOFFSETFROMPARTS

The function DATETIMEOFFSETFROMPARTS returns a datetimeoffset value for separate integer values of year, month, day, hour, minutes, seconds, fractions, precision, and time offset. The syntax for this function is DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision). The offset arguments represent the time zone offset. The same rules for precision discussed also apply to this function.

SELECT DATETIMEOFFSETFROMPARTS (2014, 02, 18, 14, 30, 00, 5, 12, 30, 1) AS MyDateValue

MyDateValue
2014-02-18 14:30:00.5 +12:30

EOMONTH 

EOMONTH returns the End-Of-Month date for the month of specified date. The syntax of the function is EOMONTH (start_date [, month_to_add]). The second argument is an optional month_to_add. This is an integer expression specifying the number of months to add to start_date before calculating the End-Of-Month date. In other words, month_to_add is added to start_date, then the function returns the last day of the month for the resulting date. If this addition overflows the valid range of dates, an error is raised. This example demonstrates the conventional usage of EOMONTH, with and without the month_to_add parameter.

SELECT EOMONTH ('02/18/2014') AS MyDateValue
MyDateValue
2014-02-18

SELECT EOMONTH ('02/18/2014', 5) AS MyDateValue
MyDateValue
2014-02-18 

SMALLDATETIMEFROMPARTS

SMALLDATETIMEFROMPARTS is very similar to DATETIMEFROMPARTS, except it returns a SmallDateTime type. The syntax for the function is SMALLDATETIMEFROMPARTS (year, month, day, hour, minute). If any of the arguments are not valid, an error is thrown. If required arguments are NULL, then NULL is returned. This function is capable of being used remotely only on servers with SQL Server 2012 servers or later. The example shows the conventional use of SMALLDATETIMEFROMPARTS. Note the resulting value contains both date and time. Since "seconds" isn't an allowed parameter, the answer simply uses "00" as the default value for seconds.

SELECT SMALLDATETIMEFROMPARTS (2014, 2, 18, 14, 30) AS MyDateValue
MyDateValue
2014-02-18 14:30:00

TIMEFROMPARTS

TIMEFROMPARTS function returns a fully initialized time value from a set of integer arguments. Note this function returns only a time value, not a date/time value. The syntax for this function is TIMEFROMPARTS (hour, minute, seconds, fractions, precision).

SELECT TIMEFROMPARTS (14, 23, 44, 500, 3) AS MyDateValue 
MyDateValue
14:23:44.500

SELECT TIMEFROMPARTS (14, 23, 44.612, 500, 3) AS MyDateValue
MyDateValue
14:23:44.500