Click here to Skip to main content
13,090,730 members (45,589 online)
Click here to Skip to main content
Add your own
alternative version


5 bookmarked
Posted 25 Feb 2014

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


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  


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

2014-02-18 14:23:44.50


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

2014-02-18 16:01:39.000


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

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


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

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


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
2014-02-18 14:30:00


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 

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


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)
Australia Australia
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

You may also be interested in...

Comments and Discussions

QuestionEOmonth Pin
Gary Henning26-Feb-14 7:07
memberGary Henning26-Feb-14 7:07 
GeneralMy vote of 5 Pin
bhargav.m.mehta25-Feb-14 20:33
memberbhargav.m.mehta25-Feb-14 20:33 
QuestionVery Nice Article Pin
bhargav.m.mehta25-Feb-14 20:32
memberbhargav.m.mehta25-Feb-14 20:32 
QuestionImage lifted from SQLSentry Pin
Darek Danielewski25-Feb-14 16:09
memberDarek Danielewski25-Feb-14 16:09 
AnswerRe: Image lifted from SQLSentry Pin
Jinesh Parekh25-Feb-14 22:04
memberJinesh Parekh25-Feb-14 22:04 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.170813.1 | Last Updated 25 Feb 2014
Article Copyright 2014 by Jinesh Parekh
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid