Free SQL Server Learning

# New Date and Time Functions in SQL Server 2012

 By: Ashish Kumar Mehta   |   Read Comments (4)   |   Related Tips: More > Dates
##### Problem

SQL Server 2012, has new date and time functions such as DATEFROMPARTS, TIMEFROMPARTS, DATETIMEFROMPARTS, DATETIME2FROMPARTS, SMALLDATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, EOMONTH that can be used to Get Date and Time Values from their parts. In this tip we take a look at some basic examples of how these can be used.

##### Solution

In this tip we will discuss how to utilize the below new date and time functions available in SQL Server 2012.

1. DATEFROMPARTS Function
2. TIMEFROMPARTS Function
3. DATETIMEFROMPARTS Function
4. DATETIME2FROMPARTS Function
5. SMALLDATETIMEFROMPARTS Function
6. DATETIMEOFFSETFROMPARTS Function
7. EOMONTH Function

Using DATEFROMPARTS Function

The DATEFROMPARTS function, which is available in SQL Server 2012, returns a date value with the date part set to the specified year, specified month and the specified day, and the time portion set to the default as shown in the below query result.

```SYNTAX: - DATEFROMPARTS (YEAR, MONTH, DAY)

/* Using DATEFROMPARTS Function */
DECLARE
@YEAR  INT = 2012
, @MONTH INT = 01
, @DAY   INT = 09

SELECT DATEFROMPARTS (@YEAR, @MONTH, @DAY) AS [Retrieved Data Using DATEFROMPARTS Function]
GO

Retrieved Data Using DATEFROMPARTS Function
-------------------------------------------
2012-01-09```

Using TIMEFROMPARTS Function

The TIMEFROMPARTS function, which is available in SQL Server 2012, returns a full time value as shown in the below query result.

It is important to note that the fractions argument actually depends on the precision argument.

For example:

1. When fractions have a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second.
2. When fractions have a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second.
3. When fractions have a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

```SYNTAX: - TIMEFROMPARTS (HOUR, MINUTE, SECONDS, FRACTIONS, PRECISION)

/* Using TIMEFROMPARTS Function */
DECLARE
@HOUR    INT = 11
, @MINUTE  INT = 59
, @SECONDS INT = 59

SELECT
TIMEFROMPARTS (@HOUR, @MINUTE, @SECONDS, 500, 3)
AS [Retrieved Data Using TIMEFROMPARTS Function]
GO

Retrieved Data Using TIMEFROMPARTS Function
-------------------------------------------
11:59:59.500```

Using DATETIMEFROMPARTS Function

The DATETIMEFROMPARTS function, which is available in SQL Server 2012, returns a full datetime value as shown in the below query result.

```SYNTAX: - DATETIMEFROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE, SECONDS, MILLISECONDS)

/* Using DATETIMEFROMPARTS Function */
DECLARE
@YEAR INT = 2012
, @MONTH INT = 01
, @DAY INT = 09
, @HOUR INT = 11
, @MINUTE INT = 59
, @SECONDS INT = 59
, @MILLISECONDS INT = 0

SELECT
DATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, @MILLISECONDS)
AS [Retrieved Data Using DATETIMEFROMPARTS Function]
GO

Retrieved Data Using DATETIMEFROMPARTS Function
-----------------------------------------------
2012-01-09 11:59:59.000```

Using DATETIME2FROMPARTS Function

The DATETIME2FROMPARTS function, which is available in SQL Server 2012, returns a full datetime2 value as shown in the below query result.

```SYNTAX: - DATETIME2FROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE, SECONDS, FRACTIONS, PRECISION)

/* Using DATETIME2FROMPARTS Function */

DECLARE
@YEAR    INT = 2012
, @MONTH   INT = 01
, @DAY     INT = 09
, @HOUR    INT = 11
, @MINUTE  INT = 59
, @SECONDS INT = 59

SELECT
DATETIME2FROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 3)
AS [Retrieved Data Using DATETIME2FROMPARTS Function]
GO

Retrieved Data Using DATETIME2FROMPARTS Function
------------------------------------------------
2012-01-09 11:59:59.500```

Using SMALLDATETIMEFROMPARTS Function

The SMALLDATETIMEFROMPARTS function, which is available in SQL Server 2012, returns a full smalldatetime value as shown in the below query result.

```SYNTAX: - SMALLDATETIMEFROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE)

/* Using SMALLDATETIMEFROMPARTS Function */

DECLARE
@YEAR   INT = 2012
, @MONTH  INT = 01
, @DAY    INT = 09
, @HOUR   INT = 11
, @MINUTE INT = 59

SELECT
SMALLDATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE)
AS [Retrieved Data Using SMALLDATETIMEFROMPARTS Function]
GO

Retrieved Data Using SMALLDATETIMEFROMPARTS Function
----------------------------------------------------
2012-01-09 11:59:00```

Using DATETIMEOFFSETFROMPARTS Function

The DATETIMEOFFSETFROMPARTS function, which is available in SQL Server 2012, returns a full datetimeoffset data type as shown in the below query result. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.

```SYNTAX: - DATETIMEOFFSETFROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE, SECONDS,
FRACTIONS, HOUR_OFFSET, MINUTE_OFFSET, PRECISION)

/* Using DATETIMEOFFSETFROMPARTS Function */

DECLARE
@YEAR    INT = 2012
, @MONTH   INT = 01
, @DAY     INT = 09
, @HOUR    INT = 11
, @MINUTE  INT = 59
, @SECONDS INT = 59

SELECT
DATETIMEOFFSETFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE,@SECONDS,500, 5, 30, 3)
AS [Retrieved Data Using DATETIMEOFFSETFROMPARTS Function]
GO

Retrieved Data Using DATETIMEOFFSETFROMPARTS Function
-----------------------------------------------------
2012-01-09 11:59:59.500 +05:30```

Using EOMONTH Function

The EOMONTH function, which is available in SQL Server 2012, calculates the last date of the month based on the date which is passed as an input parameter.

```SYNTAX: - EOMONTH (START_DATE, [, MONTH_TO_ADD ])

/* Using EOMONTH Function */
DECLARE
@STARTDATE DATETIME = GETDATE()

SELECT EOMONTH (@STARTDATE)  AS ThisMonthLastDay
SELECT EOMONTH (@STARTDATE, 1)  AS NextMonthLastDay
SELECT EOMONTH (@STARTDATE, -1) AS LastMonthLastDay
GO

ThisMonthLastDay
-----------------------
2011-12-31 00:00:00.000

NextMonthLastDay
-----------------------
2012-01-31 00:00:00.000

LastMonthLastDay
-----------------------
2011-11-30 00:00:00.000```
##### Next Steps
• Stay tuned for more tips on SQL Server 2012 and check out these related tips:

Last Update: 1/9/2012

Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources

 Print Tweet Become a paid author

All comments are reviewed, so stay on subject or we may delete your comment.

Get free SQL tips:

 *Enter Code

 Monday, January 16, 2012 - 4:10:55 PM - Jeremy Kadlec Read The Tip Ashish, What are some use cases with these functions from your tip? Thank you,Jeremy Kadlec

 Friday, January 13, 2012 - 3:42:17 PM - Carla Sabotta Read The Tip For more information about these new Date and Time functions, see these Books Online topics: DATEFROMPARTS (Transact-SQL) TIMEFROMPARTS (Transact-SQL) DATETIMEFROMPARTS (Transact-SQL)

 Friday, January 13, 2012 - 11:23:48 AM - Ashish Kumar Mehta Read The Tip Hello DavidP, Thanks for the appreciation and I am pleased to know that you looking forward to using New Features of SQL Server 2012 once the product is released. Recently, Microsoft has released the SQL Server 2012 Release Candidate (0). This shows that the SQL Server 2012 RTM will be released in the next 1 or 2 quarters. I would also recommend you to go through other MSSQLTips on SQL Server 2012 category in your free time. CheersAshish Kumar Mehta MSSQLTips - Author

 Thursday, January 12, 2012 - 11:42:59 AM - DavidP Read The Tip The code examples are pretty good.  I am looking forward to using these once SQL 2012 gets released. Does anyone know when it will be released?

## Follow

Get Free SQL Tips

Pinterest

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Tutorials

Webcasts

Whitepapers

Tools

Tip Categories

Search By TipID

Top Ten

Authors

## Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

Join