Date and Time - Get Fiscal Year Ordered Month List

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    ChillyDBA
Date:        8 Feb 2011

Description

This SP was born from a very specific requirement - the need to provide an SSRS report with a user-picklist of months, but to have the months ordered by the April=>March fiscal year.
I would have epxended some effort to make the fiscal year start configurable, but in 17 years I've only ever encountered 2 variants;  January=->December and April=>March.  The first one obviously requires no special coding.
 
Hopefully it will be helpful.  

Code

DROP PROCEDURE dbo.usp_GetFiscalYearMonthList
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*
Description:   Returns the a list of month number and names
               ordered in Fiscal Year order (ie April to March)
              
                              
Author:            ChillyDBA
History:       8 Feb 2011 - Initial Issue
              
*/

CREATE PROCEDURE dbo.usp_GetFiscalYearMonthList
AS

   SELECT
      
N                                       AS MonthNo,
      
DATENAME(MONTH,DATEADD(MONTH,N,0)-1)    AS MonthName
  
FROM
  
(
      
SELECT TOP (DATEDIFF(
                              
mm,
                              
DATEADD(mm,DATEDIFF(mm,0,'2010-04-01'),0),  --First of start month
                              
DATEADD(mm,DATEDIFF(mm,0,'2011-03-01')+1,0))
                           )
--First of month after end month
                  
N = ROW_NUMBER() OVER (ORDER BY t1.OBJECT_ID)
      
FROM master.sys.All_Columns t1
      
CROSS JOIN MASTER.sys.All_Columns t2
  
) t
      
ORDER BY DATEPART(MONTH,DATEADD(MONTH,-3,DATEADD(MONTH,N,0)-1))
      
-- exec dbo.usp_GetFiscalYearMonthList
      


ċ
usp_GetFiscalYearMonthList.sql
(1k)
Andy Hughes,
Jun 22, 2012, 7:23 AM
Comments