Wednesday, November 28, 2012

DATEFROMPARTS function in SQL Server 2012


One of the new data functions in SQL Server 2012 is the DATEFROMPARTS function. This function will give you the date when you will add the parameters to it.

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

Here is an example:

DECLARE @years TABLE (
 [Year] INT);INSERT @years([Year])SELECT 2010 AS Year 
UNION ALL 
SELECT 2011 
UNION ALL 
SELECT 2012;
SELECT [FirstDayOfYear] = DATEFROMPARTS(t1.[Year],1,1)
,      [LastDayOfYear]  = DATEFROMPARTS(t1.[Year],12,31)FROM @years y;

SELECT [FirstDayOfYear] = CONVERT(DATE,CONVERT(CHAR(8),((y.[Year] * 10000) + 101)))
,      [LastDayOfYear]  = CONVERT(DATE,CONVERT(CHAR(8),((y.[Year] * 10000) + 1231)))FROM @years y;

In this example you could see how much more easier it is to get the First day of the Year and the last day of the year with the usage of the function DATEFROMPARTS.

No comments:

Post a Comment