I always suggest that coders take a different approach.
Create a periods table in your database which has all of the dates for many years past and forward to link to. This can have numerous attributes, such as Holiday, business day, etc.
This type of thing is standard fare in a Data Warehouse, and makes so many date/week/month/year reporting tasks so much simpler, especially those reports that require groupings on non-existent data, but it also easily handles your needs, and best of all, it's codeless.
Here's a SQL Server create script for one:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AtsDWPeriod]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AtsDWPeriod]
GO
CREATE TABLE [dbo].[AtsDWPeriod] (
[PERIODID] [int] IDENTITY (1, 1) NOT NULL ,
[dtsActivityDate] [smalldatetime] NULL ,
[intYear] [int] NULL ,
[intQuarter] [int] NULL ,
[intMonthNum] [int] NULL ,
[chrMonthName] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chrMonthAbbrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intWeekNum] [int] NULL ,
[intDayNum] [int] NULL ,
[chrDayName] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chrDayAbbrev] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[intFiscalYear] [int] NULL ,
[intFiscalQuarter] [int] NULL ,
[intFiscalMonthNum] [int] NULL ,
[intFiscalWeekNum] [int] NULL ,
[bitAcctCycleEnd] [bit] NULL ,
[bitPublicHoliday] [bit] NULL ,
[bitNonWorkingDay] [bit] NULL ,
[bitSpecialWorkDay1] [bit] NULL ,
[bitSpecialWorkDay2] [bit] NULL ,
[dtmCreated] [datetime] NULL ,
[chvCreator] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
And here's a basic population SP:
CREATE PROCEDURE [dbo].[ap_DW_AtsPopulateAtsDWPeriod] AS
-- Declare the variables
declare @dtsActivityDate as smalldatetime
declare @intYear as int
declare @intQuarter as int
declare @intMonthNum as int
declare @chrMonthName as char(20)
declare @chrMonthAbbrev as char(3)
declare @intWeekNum as int
declare @intDayNum as int
declare @chrDayName as char(15)
declare @chrDayAbbrev as char(3)
declare @intFiscalYear as int
declare @intFiscalQuarter as int
declare @intFiscalMonthNum as int
declare @intFiscalWeekNum as int
declare @bitAcctCycleEnd as bit
declare @bitPublicHoliday as bit
declare @bitNonWorkingDay as bit
declare @bitSpecialWorkDay1 as bit
declare @bitSpecialWorkDay2 as bit
declare @dtmCreated as datetime
declare @chvCreator as varchar(15)
--Set the non-changing variables
set @dtmCreated = getdate()
set @chvCreator = 'Kai Molvig'
set @dtsActivityDate = '1/1/1990'
--Begin the loop
while @dtsActivityDate < '1/1/2015'
BEGIN
set @intYear = year(@dtsActivityDate )
set @intQuarter = datepart(quarter,@dtsActivityDate)
set @intMonthNum = month(@dtsActivityDate)
set @chrMonthName = datename(month,@dtsActivityDate)
set @chrMonthAbbrev = DATENAME(m,@dtsActivityDate)
set @intWeekNum = datepart(wk , @dtsActivityDate)
set @intDayNum = day(@dtsActivityDate)
set @chrDayName = datename(dw,@dtsActivityDate)
set @chrDayAbbrev = substring(@chrDayName,1,3)
-- Determine the Fiscal Year
If month(@dtsActivityDate) < 7
set @intFiscalYear = @intYear
else
set @intFiscalYear = @intYear+1
-- Determine the Fiscal Quarter
if @intQuarter = 1
set @intFiscalQuarter = 3
if @intQuarter = 2
set @intFiscalQuarter = 4
if @intQuarter = 3
set @intFiscalQuarter = 1
if @intQuarter = 4
set @intFiscalQuarter = 2
-- Determine the Fiscal Month
if @intMonthNum > 6
set @intFiscalMonthNum = @intMonthNum-6
else
set @intFiscalMonthNum = @intMonthNum+6
-- Currently not populated, perhaps drop it as it isn't used???
set @intFiscalWeekNum = 0
set @bitAcctCycleEnd = 0
-- Need to learn the holidays
set @bitPublicHoliday = 0
-- Determine whether it's a weekend - other dates might be non-working days, TBD
if datepart(dw,@dtsActivityDate) in (1,7)
set @bitNonWorkingDay = 1
else
set @bitNonWorkingDay = 0
-- Future need special work days
set @bitSpecialWorkDay1 = 0
set @bitSpecialWorkDay2 = 0
-- Do the insert
insert into atsdwperiod (dtsActivityDate, intYear, intQuarter, intMonthNum, chrMonthName, chrMonthAbbrev, intWeekNum, intDayNum, chrDayName, chrDayAbbrev, intFiscalYear, intFiscalQuarter, intFiscalMonthNum, intFiscalWeekNum, bitAcctCycleEnd, bitPublicHoliday, bitNonWorkingDay, bitSpecialWorkDay1, bitSpecialWorkDay2, dtmCreated, chvCreator)
values (@dtsActivityDate, @intYear, @intQuarter, @intMonthNum, @chrMonthName, @chrMonthAbbrev, @intWeekNum, @intDayNum, @chrDayName, @chrDayAbbrev, @intFiscalYear, @intFiscalQuarter, @intFiscalMonthNum, @intFiscalWeekNum, @bitAcctCycleEnd, @bitPublicHoliday, @bitNonWorkingDay, @bitSpecialWorkDay1, @bitSpecialWorkDay2, @dtmCreated, @chvCreator )
-- Increment the @dtsActivityDate
set @dtsActivityDate = dateadd(d,1,@dtsActivityDate)
END
GO
-k
kai@informeddatadecisions.com
