New Date and Time Functions in SQL Server 2012

New Date and Time Functions in SQL Server 2012

 

SQL Server 2012, code named Denali, has 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]
GO

Result
----------
2012-01-01 

 

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]
GO

Result
------------
11:59:59.500 

 

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]
GO

Result
-----------------------
2012-01-01 11:59:59.000 

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]
GO

Result
-----------------------
2012-01-01 11:59:59.500 

 

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]
GO

Result
-------------------
2012-01-01 11:59:00 

 

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]
GO

Result
------------------------------
2012-01-01 11:59:59.500 +05:30 

 

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]
GO

Last Date of Month
-----------------------
2012-01-31 00:00:00.000