New DateTime Function In SQL Server 2012

SQL Server 2012, code named Denali, has introduced some new DATE and TIME functions. In this post, we will discuss how these new functions are used.

Here is the list of New DATE and TIME Functions

• DATEFROMPARTS
• TIMEFROMPARTS
• DATETIMEFROMPARTS
• DATETIME2FROMPARTS
• SMALLDATETIMEFROMPARTS
• DATETIMEOFFSETFROMPARTS
• EOMONTH

DATEFROMPARTS

The DATEFROMPARTS function, 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 in the following query result.

DECLARE @YEAR INT = 2012,
@MONTH INT = 1,
@DAY INT = 1

SELECT DATEFROMPARTS (@YEAR, @MONTH, @DAY) AS [Result]

Output



TIMEFROMPARTS

The TIMEFROMPARTS function, returns a full time value as in the following query result. It is important to note that the fractions argument actually depends on the precision argument.

For example:

 

• 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.
• 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.
• 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.

 

DECLARE @HOUR INT = 11,
@MINUTE INT = 59,
@SECONDS INT = 59

SELECT TIMEFROMPARTS (@HOUR, @MINUTE, @SECONDS, 500, 3) AS [Result]

Output



DATETIMEFROMPARTS

The DATETIMEFROMPARTS function, returns a full datetime value as in the following query result.

DECLARE @YEAR INT = 2012,
@MONTH INT = 1,
@DAY INT = 9,
@HOUR INT = 11,
@MINUTE INT = 59,
@SECONDS INT = 59,
@MILLISECONDS INT = 0

SELECT DATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, @MILLISECONDS) AS [Result]

Output



DATETIME2FROMPARTS

The DATETIME2FROMPARTS function, returns a full datetime2 value as in the following query result.

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

SELECT DATETIME2FROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 3) AS [Result]

Output



SMALLDATETIMEFROMPARTS

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

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

SELECT SMALLDATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE) AS [Result]

Output



DATETIMEOFFSETFROMPARTS

The DATETIMEOFFSETFROMPARTS function, returns a full datetimeoffset data type as in the following query result. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.

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

SELECT DATETIMEOFFSETFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 5, 30, 3) AS [Result]

Output



EOMONTH

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

DECLARE @STARTDATE DATETIME = GETDATE()

SELECT EOMONTH (@STARTDATE) AS [Last Date of Month]

Output