SQL Server 2012 : New Date & Time Functions

SQL Server 2012 introduces several new Date & Time Functions. To create a SQL formatted date from component parts you would previously have to use some code similar to this.

DECLARE @YEAR INT
DECLARE @MONTH INT
DECLARE @DAY  INT

SET @YEAR = 2013
SET @MONTH = 1
SET @DAY = 17

-- Convert Version
SELECT CONVERT(DATE,CAST (@YEAR AS VARCHAR)+ RIGHT ('0'+ CAST (@MONTH AS VARCHAR),2) + RIGHT ('0'+ CAST (@DAY AS VARCHAR),2))

-- DateAdd version
SELECT DATEADD(MONTH,(@YEAR-1900)* 12 + @MONTH - 1,0) + (@DAY-1)

SQL Server 2012 provides a series of 'FROMPARTS' functions however.
The Syntax is like this ,

• DATEFROMPARTS (YEAR, MONTH, DAY)
  
• TIMEFROMPARTS (HOUR, MINUTE, SECONDS, FRACTIONS, PRECISION)
  
• DATETIMEFROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE, SECONDS, MILLISECONDS)
  
• DATETIME2FROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE, SECONDS, FRACTIONS, PRECISION)
  
• SMALLDATETIMEFROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE)
  
• DATETIMEOFFSETFROMPARTS (YEAR, MONTH, DAY, HOUR, MINUTE, SECONDS, FRACTIONS, HOUR_OFFSET, MINUTE_OFFSET, PRECISION)

In Addition an end of month function is available too.

• EOMONTH (START_DATE, [, MONTH_TO_ADD ])

Their use is demonstrated below.

DECLARE 
  @YEAR INT = 2013
, @MONTH INT = 01 
, @DAY INT = 09 
, @HOUR INT = 11
, @MINUTE INT = 59 
, @SECONDS INT = 59
, @MILLISECONDS INT = 0 
, @TODAY DATETIME = GETDATE()

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

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

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

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

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

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

SELECT EOMONTH (@TODAY)  AS LASTDAYTHISMONTH
SELECT EOMONTH (@TODAY, -1) AS LASTDAYLASTMONTH
SELECT EOMONTH (@TODAY, 1)  AS LASTDAYNEXTMONTH

Links :
SQL Server 2012 New Date Functions

New Date,Logical Functions in SQL Server 2012

The ultimate guide to the datetime datatypes