Sql Server 2012- Date time new function

Sql Server 2012- Date time new function

Following are the seven new date time functions have been introduced in SQL Server 2012

1:- DATEFROMPARTS function

The DATEFROMPARTS function is introduced in SQL Server 2012. it returns a date value with the date parts set to the specified year, month and day.

Syntax: - DATEFROMPARTS(year, month, day)

Example:-

DECLARE @YEAR  INT = 2016,
        @MONTH INT = 10, 
        @DAY   INT = 24

SELECT DATEFROMPARTS (@YEAR, @MONTH, @DAY) AS Using_DATEFROMPARTS
GO

Output:-

Using_DATEFROMPARTS
-------------------
2016-10-24

2:- DATETIME2FROMPARTS function

The DATETIME2FROMPARTS function returns a full datetime2 value.

Syntax: - DATETIME2FROMPARTS(year, month, day, hour, minute, seconds, fractions, precision)

Example:-

DECLARE @YEAR INT = 2016, @MONTH INT = 10, @DAY INT = 24,
         @HOUR INT = 17, @MINUTE INT = 44, @SECONDS INT = 45

SELECT
 DATETIME2FROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 600, 3)
 AS Using_DATETIME2FROMPARTS
GO

OUTPUT:-

Using_DATETIME2FROMPARTS
------------------------
2016-10-24 17:44:45.600

3:- DATETIMEFROMPARTS function

The DATETIMEFROMPARTS function is introduced  in SQL Server 2012. It returns a full datetime value.

Syntax:- DATETIMEFROMPARTS(year, month, day, hour, minute, seconds, milliseconds)

Example:-

DECLARE @YEAR INT = 2016, @MONTH INT = 10, @DAY INT = 24,
         @HOUR INT = 17, @MINUTE INT = 44, @SECONDS INT = 45,
         @MILLISECONDS INT=0

SELECT
 DATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, @MILLISECONDS)
 AS Using_DATETIMEFROMPARTS
GO

OUTPUT:-

Using_DATETIMEFROMPARTS
------------------------
2016-10-24 17:44:45.000

4:- DATETIMEOFFSETFROMPARTS function

The DATETIMEOFFSETFROMPARTS function is introduced in SQL Server 2012. It returns a full datetimeoffset data type.

Syntax:-

DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)

Example:- 

DECLARE  @YEAR INT = 2016, @MONTH INT = 10, @DAY INT = 24,
         @HOUR INT = 17, @MINUTE INT = 44, @SECONDS INT = 45        

SELECT
 DATETIMEOFFSETFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE,@SECONDS,500, 5, 30, 3)
 AS Using_DATETIMEOFFSETFROMPARTS
GO

OUTPUT:-

Using_DATETIMEOFFSETFROMPARTS
-----------------------------
2016-10-24 17:44:45.500 +05:30

5:- SMALLDATETIMEFROMPARTS function

The SMALLDATETIMEFROMPARTS function is introduced in SQL Server 2012. It returns a full smalldatetime value.

Syntax:- SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)

Example:-

DECLARE  @YEAR INT = 2016, @MONTH INT = 10, @DAY INT = 24,
         @HOUR INT = 17, @MINUTE INT = 44, @SECONDS INT = 45        

SELECT
 SMALLDATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE)
 AS Using_SMALLDATETIMEFROMPARTS
GO

OUTPUT:-

Using_SMALLDATETIMEFROMPARTS
----------------------------
2016-10-24 17:44:00

6:- TIMEFROMPARTS function

The TIMEFROMPARTS function is introduced in SQL Server 2012. It returns a full time value.

Syntax:- TIMEFROMPARTS (hour, minute, seconds, fractions, precision)

Example:- 

DECLARE @HOUR INT = 17, @MINUTE INT = 44, @SECONDS INT = 45
        

SELECT
  TIMEFROMPARTS (@HOUR, @MINUTE, @SECONDS, 500, 3)
 AS Using_TIMEFROMPARTS
GO

OUTPUT:-

Using_TIMEFROMPARTS
-------------------
17:44:45.500

7:- EOMONTH function

This is new datetime function that is introduced in sql server 2012. This function can be used to get the last day of the month. We can Use of EOMONTH() function to find last day Previous and Next Month.

Syntax:- EOMONTH (date)

Examples:-

Example 1: Find last day of the month

SELECT EOMONTH(GETDATE()) LastDayofMonth

OUTPUT:-

LastDayofMonth
--------------
2016-10-31

Example 2: Find last day of the month during Leap year

SELECT EOMONTH('20070201') LastDayofNonLeapYearFeb,
       EOMONTH('20080201') LastDayofLeapYearFeb,
       EOMONTH('20090201') LastDayofNonLeapYearFebLastDay;

OUTPUT:-

LastDayofNonLeapYearFeb  LastDayofLeapYearFeb    LastDayofNonLeapYearFebLastDay
------------------------------------------------------------------------------
2007-02-28                2008-02-29              2009-02-28

Example 3: Find last day Previous and Next Month

SELECT EOMONTH(GETDATE(),-1) LastDayOfPreviousMonth,
       EOMONTH(GETDATE(),1) LastDayOfNextMonth


OUTPUT:-

LastDayOfPreviousMonth    LastDayOfNextMonth
--------------------------------------------
2016-09-30               2016-11-30