New Date and Time Functions in SQL Server 2012

SQL Server 2012, code named Denali, introduced some new DATE and TIME Functions. In this post, we will discuss on 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 shown in the below 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 shown in the below 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 shown in the below 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 shown in the below 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 shown in the below 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 shown in the below 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