DATEFROMPARTS
- by jamiet
I recently overheard a remark by Greg Low in which he said something akin to "the most interesting parts of a new SQL Server release are the myriad of small things that are in there that make a developer's life easier" (I'm paraphrasing because I can't remember the actual quote but it was something like that).
The new DATEFROMPARTS function is a classic example of that . It simply takes three integer parameters and builds a date out of them (if you have used DateSerial in Reporting Services then you'll understand).
Take the following code which generates the first and last day of some given years:
SELECT 2008 AS Yr INTO #Years UNION ALL SELECT 2009 UNION ALL SELECT 2010 UNION ALL SELECT 2011 UNION ALL SELECT 2012SELECT [FirstDayOfYear] = CONVERT(DATE,CONVERT(CHAR(8),((y.[Yr] * 10000) + 101))), [LastDayOfYear] = CONVERT(DATE,CONVERT(CHAR(8),((y.[Yr] * 10000) + 1231)))FROM #Years y
here are the results:
That code is pretty gnarly though with those CONVERTs in there and, worse, if the character string is constructed in a certain way then it could fail due to localisation, check this out:
SET LANGUAGE french;SELECT dt,Month_Name=DATENAME(mm,dt)FROM ( SELECT dt = CONVERT(DATETIME,CONVERT(CHAR(4),y.[Yr]) + N'-01-02') FROM #Years y )d;SET LANGUAGE us_english;SELECT dt,Month_Name=DATENAME(mm,dt)FROM ( SELECT dt = CONVERT(DATETIME,CONVERT(CHAR(4),y.[Yr]) + N'-01-02') FROM #Years y )d;
Notice how the datetime has been converted differently based on the language setting. When French, the string "2012-01-02" gets interpreted as 1st February whereas when us_english the same string is interpreted as 2nd January.
Instead of all this CONVERTing nastiness we have DATEFROMPARTS:
SELECT [FirstDayOfYear] = DATEFROMPARTS(y.[Yr],1,1), [LasttDayOfYear] = DATEFROMPARTS(y.[Yr],12,31)FROM #Years y
How much nicer is that? The bad news of course is that you have to upgrade to SQL Server 2012 or migrate to SQL Azure if you want to use it, as is the way of the world!
Don't forget that if you want to try this code out on SQL Azure right this second, for free, you can do so by connecting up to AdventureWorks On Azure. You don't even need to have SSMS handy - a browser that runs Silverlight will do just fine. Simply head to https://mhknbn2kdz.database.windows.net/ and use the following credentials:
Database AdventureWorks2012
User sqlfamily
Password sqlf@m1ly
One caveat, SELECT INTO doesn't work on SQL Azure so you'll have to use this instead:
DECLARE @y TABLE ( [Yr] INT);INSERT @y([Yr])SELECT 2008 AS Yr UNION ALL SELECT 2009 UNION ALL SELECT 2010 UNION ALL SELECT 2011 UNION ALL SELECT 2012;SELECT [FirstDayOfYear] = DATEFROMPARTS(y.[Yr],1,1), [LastDayOfYear] = DATEFROMPARTS(y.[Yr],12,31)FROM @y y;SELECT [FirstDayOfYear] = CONVERT(DATE,CONVERT(CHAR(8),((y.[Yr] * 10000) + 101))), [LastDayOfYear] = CONVERT(DATE,CONVERT(CHAR(8),((y.[Yr] * 10000) + 1231)))FROM @y y;
@Jamiet
