THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

DATEFROMPARTS

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 2012

SELECT [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

    Published Thursday, May 10, 2012 10:53 AM by jamiet
    Filed under: ,

    Comment Notification

    If you would like to receive an email when updates are made to this post, please register here

    Subscribe to this post's comments using RSS

    Comments

     

    Jānis said:

    i do prefer this much more:

    http://programmingmaterials.blogspot.com/2009/09/sqlserver-useful-sql-server-datetime.html, because there is no cast to character and seems to be faster :).

    May 10, 2012 10:00 AM
     

    Aaron Bertrand said:

    Interesting post Jamie, thanks for sharing! However just one clarification: YYYYMMDD will never fail due to regional or language settings.

    May 14, 2012 4:40 PM
     

    jamiet said:

    Hey Aaron,

    yeah, I know. I was a bit lazy when I wrote this - I should have come up with an example where localisation would be an issue, instead I just threw out "if the character string is constructed in a certain way then it could fail due to localisation". Thanks for pointing it out.

    JT

    May 15, 2012 3:44 AM
     

    jamiet said:

    ...and actually I figured it would be best if I updated the post accordingly. It now has an example of where localisation can cause problems.

    May 15, 2012 4:03 AM

    Leave a Comment

    (required) 
    (required) 
    Submit

    This Blog

    Syndication

    Powered by Community Server (Commercial Edition), by Telligent Systems
      Privacy Statement