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

*/