Here we go a SQL-ing among the leaves so green…

While I code at work, I listen to my Zune, and at this time of year, I’m listening to my holiday songs. One of my favorite holiday songs is “The Twelve Days of Christmas”, in its various forms – the Starcraft version (for the gamer geek in me), Straight No Chaser’s versionJohn Denver and the Muppets, and even the 12 Pains of Christmas. But many people sing the song without really knowing what the 12 days are.

I always wondered if it was the 12 days leading up to the Christmas in December (12/25) or January (1/6). Sparing the whole religious story, the 12 days of Christmas are actually the 12 nights leading up to the January Christmas, with the 1st day of Christmas starting on 12/25.

I was looking at stored procedures when this song came across, and it inspired the following query:

DECLARE @DaysOfChristmas int,
@DateOfChristmas smalldatetime,
@ChristmasDay smalldatetime,
@GiftOfTheDay varchar(50)

SELECT @ChristmasDay = '12/25/2008',
@DateOfChristmas = @ChristmasDay,
@DaysOfChristmas = DATEDIFF(d, @ChristmasDay, @DateOfChristmas)

WHILE (@DaysOfChristmas + 1 <= 12)
SELECT @GiftOfTheDay =
CASE @DaysOfChristmas + 1
WHEN 1 THEN 'partridge in a pear tree'
WHEN 2 THEN 'turtle doves'
WHEN 3 THEN 'french hens'
WHEN 4 THEN 'calling birds'
WHEN 5 THEN 'golden rings'
WHEN 6 THEN 'geese a-laying'
WHEN 7 THEN 'swans a-swimming'
WHEN 8 THEN 'maids a-milking'
WHEN 9 THEN 'ladies dancing'
WHEN 10 THEN 'lords a-leaping'
WHEN 11 THEN 'pipers piping'
WHEN 12 THEN 'drummers drumming'

PRINT CONVERT(varchar(10),@DateOfChristmas,101) + ': ' + CONVERT(varchar(2),@DaysOfChristmas+1) + ': ' + @GiftOfTheDay

SELECT @DateOfChristmas = DATEADD(d,1,@DateOfChristmas), @DaysOfChristmas = DATEDIFF(d, @ChristmasDay, @DateOfChristmas)


The output looked like this:

12/25/2008: 1: partridge in a pear tree
12/26/2008: 2: turtle doves
12/27/2008: 3: french hens
12/28/2008: 4: calling birds
12/29/2008: 5: golden rings
12/30/2008: 6: geese a-laying
12/31/2008: 7: swans a-swimming
01/01/2009: 8: maids a-milking
01/02/2009: 9: ladies dancing
01/03/2009: 10: lords a-leaping
01/04/2009: 11: pipers piping
01/05/2009: 12: drummers drumming

So now, I know which traditional gift is appropriate for which date.

If my blogging is slow over the next couple weeks, it’s because this geekette spends her time making cookies, of the non-HTTP type. I will be spending time away from the computer and with family and friends. I will also be preparing for CodeMash. If you are going to be there, come say “hi”! I hope to see many of you there!

So to those of my readers who celebrate the various holidays this season, have a safe and enjoyable holiday season!

By sadukie

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.