By: Ashish Kumar Mehta | Updated: 2012-01-09 | Comments (5) | Related: > 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:30Using 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:
About the author
Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2012-01-09
