Getting month names from thin air
Today I decided to make a change to one of the model reports I'm constructing from a Service Desk product. The report allows for the user to select a given month and generate statistics from it. When I originally created the report I manually populated the selection list. However, as I am working on some additional models and I'm trying to be conscientious of language flexibility, I figured I would rather have a script I can paste in to generate the list dynamically. That thought produced a need for a tally table that does not exist in the database. Since I'm only looking for a short list I figured I'd have a go at it with a CTE. Here's the script that goes through and produces a two column result set containing the localized month name and the ordinal position it has.
There's probably an easier way to do it, as my logic for producing the months might be a little excessive, but I wanted to avoid using a cursor and having any problems with the end of the month numbers.
/******************************************************************
Populate a CTE with the numbers 1-12
Produce a list of month names for each month number
Find the first day of the current month
Subtract the month number from the current date and add n
to produce the first of the month.
Capture the month name, and the n value.
Command used:
DateName(Part, Date)
DateAdd(Part, Number, Date)
DatePart(Part, Date)
*******************************************************************/
WITH tally ( n )
AS ( SELECT CAST(1 AS BIGINT) AS 'n'
UNION ALL
SELECT ( T.n + 1 ) AS 'n'
FROM tally T
WHERE n < 12
)
SELECT DATENAME(MONTH,
DATEADD(Month,
DATEPART(MONTH,
DATEADD(Day, 1,
GETDATE() - DAY(GETDATE()) + 1)
- 1) * -1 + n,
DATEADD(Day, 1, GETDATE() - DAY(GETDATE()) + 1)
- 1)) [MonthName] ,
n [MonthNum]
FROM tally
