"SQL Spackle" is a collection of short articles written based on multiple requests for similar code. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks".

--Phil McCracken

Introduction

Most people get it. They correctly store dates as the DATETIME datatype. The trouble comes when they have to display the month name as a full month name and still have to sort the data correctly. The normal thing to do is "let the GUI do it" but, sometimes, that's just not possible. These problems only get worse when you need to do a GROUP BY on the dates by month and need to sort the month correctly. How do you sort those? Here's a little "SQL Spackle" for some of the more common problems in those areas.

Test Data

You just can't appreciate any code without some test data. As always, the details are in the code:

--=============================================================================
--      Create the test data.  This is NOT a part of the solution.
--      Usually takes something less than 12 seconds to generate.
--=============================================================================
--===== Conditionally drop the test table to make reruns easier.
     IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL
        DROP TABLE #MyHead
;
GO
--===== Create the table and populate it on the fly.
     -- This builds a table with random dates and amounts for 20 years starting
     -- in the year 2005.
 SELECT TOP (1000000)
        SomeDateTime = RAND(CHECKSUM(NEWID()))*7305 + CAST('2005' AS DATETIME),
        SomeAmount   = CAST(RAND(CHECKSUM(NEWID()))*100 AS MONEY)
   INTO #MyHead
   FROM master.sys.all_columns ac1
  CROSS JOIN master.sys.all_columns ac2
;
--===== Add a clustered index to the table just to keep it from being a heap
     -- and to help with speed a bit by sorting the data.
 CREATE CLUSTERED INDEX IX_#MyHead_SomeDateTime
     ON #MyHead (SomeDateTime)
;

Creating the Names

Sorting DATETIME datatypes is easy. You just do an ORDER BY on the DATETIME column and dates are sorted perfectly. But how do you display just the month name? Most people end up making a scalar function with a bunch of CASE statements like WHEN MONTH(SomeDate) = 1 THEN 'January' and it's just not necessary. There's a date function that will do that for us. A lot of people have never heard of it but it's called DATENAME and it returns the fully spelled out name of the month in whatever the current SQL Server language settings are.

Here's an example of how it works with a GROUP BY and no sort (we're trying to just get totals for the year 2010):

--===== SUM the data for each month and sort the month names correctly.
     -- This one uses the full name of each month but doesn't sort.
     -- Notice how using just the year works here because it will be
     -- converted to the 1st of January when it's converted to DATETIME.
 SELECT [Month] = DATENAME(mm,SomeDateTime),
        Amount  = SUM(SomeAmount)
   FROM #MyHead
  WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
  GROUP BY DATENAME(mm,SomeDateTime)
;

Of course, that won't give us a sort.

Month                          Amount
------------------------------ ---------------------
February                       193412.1037
June                           206262.1652
August                         221954.1671
April                          207428.5187
May                            209381.6455
December                       208184.9687
September                      201854.8769
January                        209178.9661
March                          209960.7545
October                        212573.3348
July                           208471.9746
November                       201869.4159

Sorting the Month Names

To make a very long story short, you try all sorts of different things including CTE's and UDF's and you eventually get it to sort correctly but the code gets pretty long an ugly because the ORDER BY has to be something in the GROUP BY or you get an error like the following:

Msg 8127, Level 16, State 1, Line 3

Column "#MyHead.SomeDateTime" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

It's much simpler to use what you have available. We've already seen how we could use just the year as a date for the first of January. We can do something similar to convert the month name back to a sortable date. First, ask yourself, does it really matter what year it is when we do this sort??? No... 1900 (or any year) will do just fine. Notice that the ORDER BY doesn't have to contain ONLY what's in the SELECT list. You can add a conversion and a constant with no problem which solves the sort problem.

--===== SUM the data for each month and sort the month names correctly.
     -- This one uses the full name of each month AND sorts them!
 SELECT [Month] = DATENAME(mm,SomeDateTime),
        Amount  = SUM(SomeAmount)
   FROM #MyHead
  WHERE SomeDateTime >= '2010' AND SomeDateTime < '2011'
  GROUP BY DATENAME(mm,SomeDateTime)
  ORDER BY CAST(DATENAME(mm,SomeDateTime) + ' 1900' AS DATETIME)
;

That gives us the following output:

Month                          Amount
------------------------------ ---------------------
January                        209178.9661
February                       193412.1037
March                          209960.7545
April                          207428.5187
May                            209381.6455
June                           206262.1652
July                           208471.9746
August                         221954.1671
September                      201854.8769
October                        212573.3348
November                       201869.4159
December                       208184.9687

And, "YES"... it does an Index Seek.

Crack filled! 😉

Thanks for listening, folks.

--Jeff Moden