"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