Click here to Skip to main content
Click here to Skip to main content

SQL 2012 - Newly Introduced Date and Time Functions

, 25 Feb 2014
Rate this:
Please Sign up or sign in to vote.
Newly introduced Date and Time functions in SQL 2012

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Jinesh Parekh
Software Developer (Senior) Capgemini
India India
An IT Professional with over 10 years of experience in system analysis, components design, development and testing of client server, Intranet/ Internet, N-tier systems, Have developed applications using technologies like MVC, EDMS, XML, Infopath, SQL Server 2005/2008/2012, SSIS, SSRS, Oracle, Actuate and Crystal Reports. Has excellent analytical and troubleshooting skills exhibited by efficient monitoring and support of production applications. Proven leadership and strong interpersonal and communication skills in dealing with people with diverse backgrounds
Follow on   LinkedIn

Comments and Discussions

 
QuestionEOmonth PinmemberGary Henning26-Feb-14 7:07 
GeneralMy vote of 5 Pinmemberbhargav.m.mehta25-Feb-14 20:33 
QuestionVery Nice Article Pinmemberbhargav.m.mehta25-Feb-14 20:32 
QuestionImage lifted from SQLSentry PinmemberDarek Danielewski25-Feb-14 16:09 
AnswerRe: Image lifted from SQLSentry PinmemberJinesh Parekh25-Feb-14 22:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 25 Feb 2014
Article Copyright 2014 by Jinesh Parekh
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid