Get List of Months or Days
SQL Server 2000: Not Supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 11 Apr 2012
The title might suggest that this tip should be in the Date/Time category, but this doesn't actually do any date or time mainupulation.
Most of the instances where the text version of the month/day is required, it is extracted inline using the built-in DATENAME function. If there is month/day ordering required in these cases, then this can be achieved by leveraging the DATEPART function with Month/Day parameter.
The cases where these particular functions come into their own is for list population in interactive reports.
Looking at the functions, you might be tempted to ask the benefit of a CTE against just coding a fixed UNION type query returning the lists. The benefit of a CTE is regionalisation - it uses the numerical values to extract and order the Months/Days, but the textual portion of the list will be returned in the language of the SQL Server.
There are also 2 numeric columns returned by the functions.
With the day function, these represent the calendar ordering with the default start week of Monday (DayNumber) and the offset week implemented using a custom value for the @@DATESTART system global variable.
With the month function, they will currently aways be the same and are included for consistency. However, the month order may be customised to reflect business year logic.
Get List of Months:
Get List of Days: