Get List of Months or Days
Applicability:
SQL Server 2000: Not Supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: Unknown/ChillyDBA
Date: 11 Apr 2012
Description
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.
Code
Get List of Months:
DROP FUNCTION dbo.udf_GetMonthList
GO
CREATE FUNCTION dbo.udf_GetMonthList ()
RETURNS TABLE
AS
RETURN
(
WITH CTEMonth
AS
(
SELECT 1 AS MonNum
UNION ALL
SELECT MonNum + 1 -- add month number to 1 recursively
FROM CTEMonth
WHERE MonNum < 12 -- just to restrict the monthnumber upto 12
)
SELECT
MonNum AS MonthOrder, -- the logical ordering of the months
MonNum AS MonthNumber, -- the actual ordering of the months (will always be the same as the logical ordering)
DATENAME(MONTH,DATEADD(MONTH,MonNum,0)- 1)[MonthName] -- function to list the monthname.
FROM CTEMonth
)
GO
--select * from dbo.udf_GetMonthList()
Get List of Days:
DROP FUNCTION dbo.udf_GetDayList
GO
CREATE FUNCTION dbo.udf_GetDayList ()
RETURNS TABLE
AS
RETURN
(
WITH CTEDay
AS
(
SELECT 1 AS DayNum
UNION ALL
SELECT DayNum + 1 -- add day number to 1 recursively
FROM CTEDay
WHERE DayNum < 7 -- just to restrict the daynumber upto 12
)
SELECT
DATEPART(DW,DATEADD(DAY,DayNum,0)- 1) DayOrder,
DayNum AS DayNumber, -- the actual ordering of the days (may be different if @@DATEFIRST is different from the default 1)
DATENAME(DW,DATEADD(DAY,DayNum,0)- 1)[DayName] -- function to list the dayname.
FROM CTEDay
)
/*
select * from dbo.udf_GetDayList()
SET DATEFIRST 3
GO
SET DATEFIRST 5
select * from dbo.udf_GetDayList()
GO
SET DATEFIRST 1
select * from dbo.udf_GetDayList()
GO
*/