Introduction

Accountants have devised various means to break down financial periods for their purposes. Most people are familiar with a fiscal year, but another dividing technique is referred to as a 4-4-5 Calendar. This practice involves breaking a year down into quarters, just like a fiscal year, but the difference is that each quarter is divided into two 4-week periods and one 5-week period. It can be organized as 5-4-4 or 4-5-4, but the 4-4-5 breakdown is the most common. One advantage of this technique is that each period ends on the same day of the week. This is useful when planning shift or manufacturing work. A disadvantage is that this technique results in most years having 364 days and every few years there is a year with extra days. Both its advantages and disadvantages create particular issues for a programmer trying to generate reports based on this technique. In this two-part series, I will offer possible solutions for determining both the 4-4-5 Year of a given date and the 4-4-5 Period of a given date.

Function

In this first part of the series I will focus on the 4-4-5 Year function. The purpose of the function is to return a string which denotes the "Year" in which a given date falls. A given date, for example 7/2/2009, has an actual year (2009) which has a range of 1/1/2009 to 12/31/2009. But if the fiscal year starts on 7/1/2009, then 7/2/2009 is in the fiscal year 2010 with the range now being 7/1/2009 to 6/30/2010. But, when it comes to a 4-4-5 scenario, things get a little more complicated.

The first complication is that the year does not always start on the same date. It should always start on the same day of the week (e.g. Sunday), but the date changes. For example, the first Sunday in 2008 is on the 6th of January. But in 2009, the first Sunday is on the 4th. The second complication is that the last day of the year may fall in the next physical year. Again, as an example, the last day of 2008 is actually on 1/3/2009.

The following function is useful in queries to return the 4-4-5 accounting year for a given date. The parameters for the function are a starting point for the corporate fiscal year, the date that you want to test, and the weekday to start the counting. The starting point of the fiscal year should be represented by the first day of the month for the fiscal year and the physical year 1900, such as 7/1/1900. The function returns a string in the format "Year yyyy (Starting mm/dd/yyyy)" where yyyy is the year in which the test date falls, and mm/dd/yyyy is the first date in which the given weekday occurs that year.

/*
 Takes in a Fiscal Year and a Date
 Returns a string representing the year in which the date falls
 based on a 4-4-5 accounting method.
*/
CREATE FUNCTION [dbo].[fn_445AccountingYear](
 @Fiscal datetime, -- The Month and Day are the relevant pieces to determine the fiscal period start date
 @TestDate datetime,
 @Weekday int-- The day of the week to start accounting: 1-Sunday, 2-Monday, etc.
) 
RETURNS varchar(50) AS
BEGIN 
DECLARE @FiscalStart datetime
DECLARE @FiscalStartAdjusted datetime
-- Determine the Fiscal Start Date for the year of the given date.
SELECT @FiscalStart = DateAdd(year, datediff(year, IsNull(@Fiscal,'1/1/1900'), @TestDate) , IsNull(@Fiscal,'1/1/1900'))
-- Adjust the Fiscal Start to the first @Weekday in the Fiscal year.
SELECT @FiscalStartAdjusted = CASE WHEN DatePart(dw, @FiscalStart) = @Weekday THEN @FiscalStart 
 WHEN DatePart(dw, @FiscalStart) < @Weekday THEN DateAdd(d, @Weekday - DatePart(dw, @FiscalStart), @FiscalStart)
 ELSE DateAdd(d, @Weekday - DatePart(dw, @FiscalStart) + 7, @FiscalStart) END
-- Determine the year for the date
IF (@TestDate < @FiscalStartAdjusted)
BEGIN
 -- Have to find the starting date on the previous year
 SELECT @FiscalStartAdjusted = DateAdd(year, -1, @FiscalStart)
 -- Adjust the Fiscal Start to the first @Weekday again because the same day the previous year is not the same day of the week and leap years
 -- change it by two days.
 SELECT @FiscalStartAdjusted = CASE WHEN DatePart(dw, @FiscalStartAdjusted) = @Weekday THEN @FiscalStartAdjusted 
 WHEN DatePart(dw, @FiscalStartAdjusted) < @Weekday THEN DateAdd(d, @Weekday - DatePart(dw, @FiscalStartAdjusted), @FiscalStartAdjusted)
 ELSE DateAdd(d, @Weekday - DatePart(dw, @FiscalStartAdjusted) + 7, @FiscalStartAdjusted) END
END
RETURN 'Year ' + Convert(varchar,Year(@FiscalStartAdjusted)) + ' (Starting ' + Convert(varchar,@FiscalStartAdjusted, 101) + ')'
END

First, determine the starting date for the fiscal year of the testdate. Do this by taking the difference in years between the supplied test date and the fiscal date and then adding that difference back to the fiscal date. This gives a fiscal start date. Next, adjust that date to the first day of the week supplied in @Weekday that occurs after that fiscal start date. This is accomplished here using a CASE statement. If the weekday of the fiscal start date is the same as @Weekday, then use the fiscal start date. If the weekday of the fiscal start date is less than @Weekday, then take the difference between the two and add that to the fiscal start date. If the weekday of the fiscal start date is greater than @Weekday, then also take the difference between the two and add that to the fiscal date but then add seven days to get it to the next week. This gives a fiscal adjusted start date that is the first date that @Weekday occurs in the current fiscal year.

At this point the work would be done except if the test date actually falls before @FiscalStartAdjusted. In this case, the test date belongs in the previous year. To fix this, subtract a year from the original fiscal start date (@FiscalStart). Then, reapply the logic to adjust the date to the first @Weekday of the year. Finally, format the string to return.

Samples

To demonstrate the function, create a table with a fiscal date field and a test date field.

IF EXISTS(SELECT * from sysobjects WHERE xtype = 'U' and name = 'TestDates')
DROP TABLE TestDates
GO

CREATE TABLE TestDates (
 FiscalStartDate datetime,
 TestDate datetime
)
GO

Insert an assortment of data into the table. The following sample data can help show how a date that appears in a particular physical year will actually belong in the previous year based on the criteria for the accounting function.

INSERT INTO TestDates VALUES ('1/1/1900', '12/31/2008')
INSERT INTO TestDates VALUES ('1/1/1900', '1/1/2009')
INSERT INTO TestDates VALUES ('1/1/1900', '1/2/2009')
INSERT INTO TestDates VALUES ('1/1/1900', '1/4/2009')
INSERT INTO TestDates VALUES ('1/1/1900', '1/5/2009')
INSERT INTO TestDates VALUES ('1/1/1900', '5/1/2009')
INSERT INTO TestDates VALUES ('1/1/1900', '1/1/2010')
INSERT INTO TestDates VALUES ('7/1/1900', '6/30/2009')
INSERT INTO TestDates VALUES ('7/1/1900', '7/1/2009')
INSERT INTO TestDates VALUES ('7/1/1900', '7/2/2009')
INSERT INTO TestDates VALUES ('7/1/1900', '7/6/2009')
INSERT INTO TestDates VALUES ('7/1/1900', '7/7/2009')
INSERT INTO TestDates VALUES ('7/1/1900', '12/31/2009')

Next, call the function in a simple SELECT statement. I return the original FiscalStartDate and TestDate from the table so that I can see the two pieces of information that give the output string. Here, I hard code a 1 for @Weekday to start the accounting on a Sunday.

SELECT 
 CONVERT(varchar,FiscalStartDate,101) AS FiscalStartDate, 
 CONVERT(varchar,TestDate,101) AS TestDate, 
 dbo.fn_445AccountingYear(FiscalStartDate, TestDate, 1) AS [445Value]
FROM TestDates

The first seven results are based on the idea that the accounting year starts on January 1st. Each 4-4-5 Year will actually start on a different day each year based on when the first Sunday falls. From the results, notice that 1/1/2009 and 1/2/2009 fall in the year 2008 because the first Sunday of 2009 is not until 1/4/2009. A similar thing happens if the accounting year starts on July 1st, as shown where 7/1/2009 and 7/2/2009 fall in the 2008 4-4-5 Year.

Conclusion

Determining a date's year based on a 4-4-5 accounting method is fairly simple using this interpretation. Find the first @Weekday, adjust the year if needed, and return the string. Now, if you do a little digging you may find that sometimes a year will end up with an extra week, like 2006. This is an unfortunate side effect of this accounting method. There are other interpretations of how to do the actual accounting, but this method has worked well in my applications and is straight forward.

A more difficult task is to determine a date's 4-4-5 "Period". The Period is similar to a month designation. There are certain boundaries that must be calculated that make the function a little more complicated. Look for the implementation and explanation of the 4-4-5 Period in Part 2.