Cool Tips‎ > ‎Data Formatting‎ > ‎

Data Formatting - 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

*/

 
ċ
Return ordered list of months or days.sql
(1k)
Andy Hughes,
Jun 11, 2012, 11:37 AM
Comments