Fiscal Year

In reality we often are required to provide statistics based on fiscal year/quarter, for example, the fiscal year might start on Nov 1 every year, and the first fiscal quarter ends on Jan 31 next year, and so on.

To get the right data based on this new “year” and “quarter”, we need to redefine the start of year and quarter.

The basic idea is to calculate the years between the given date and the base datetime 1900-01-01 00:00:00.000, we can use DATEDIFF function to do that:

DATEDIFF(datepart , startdate , enddate)

From SQL online help:

datepart

Is the part of startdate and enddate that specifies the type of boundary crossed

startdate

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable or string literal. startdate is subtracted from enddate

Enddate

                Same as startdate

We will also use DATEADD in the calculation

DATEADD(datepart, number, date)

From SQL online help:

datepart

Is the parameter that specifies which part of the date to add a number to.

number

Is the value used to increment datepart. The value must be an integer value that is known when the expression is parsed.

date

Is an expression that returns a valid date or a string in date format.

code step by step:

1. Get the number of years between a given date and base datetime 1900-01-01 00:00:00.000

   SELECT DateDiff(yy,0,Getdate())

   This returns 114 for this year 2014

2. For cases that Fiscal year starts at a different date instead of January 1, we need to add the difference in. For example, if the case that Fiscal year starts on November 1, that means the year starts two month earlier, we need to slightly modify the code to

         SELECT DateDiff(yy,0,DateAdd(mm,2,Getdate()))

If we run this query now (mid of the year), there is no difference than the previous query, but if we run this on October 31 and compare it with November 1, we will see the difference:

Declare @CurrentDate datetime
Set @CurrentDate = '2013-10-31'
SELECT DateDiff(yy,0,@CurrentDate)

Returns 113

SELECT DateDiff(yy,0,DateAdd(mm,2,@CurrentDate))

Returns 113

Now let's do the same check to a new date: 2013-11-01

Declare @CurrentDate datetime
Set @CurrentDate = '2013-11-01'
SELECT DateDiff(yy,0,@CurrentDate)

Still returns 113

SELECT DateDiff(yy,0,DateAdd(mm,2,@CurrentDate))

Returns 114

So now we have the right number of years gap, we convert the gap into the first date of the year:

SELECT DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)

This returns: 2014-01-01 00:00:00.000

Note we need to make the adjustment to the real Fiscal year, so it would be:

SELECT DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))

This returns: 2013-11-01 00:00:00.000

Note in the DateAdd second argument, it’s -2 if the Fiscal year starts on November 1, meaning 2013-11-01 is the Fiscal year 2014

Here is an example to use the new starting date in other query:

SELECT
     FYStart
     ,FYNextStart = DateAdd(yy,1,FYStart)
     ,FYPrevStart = DateAdd(yy,-1,FYStart)
FROM
     (SELECT FYStart = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) dt1

It returns:

FYStart                 FYNextStart             FYPrevStart
2013-11-01 00:00:00.000 2014-11-01 00:00:00.000 2012-11-01 00:00:00.000

Example of using it with other query:

SELECT * FROM dbo.Document  d
    CROSS JOIN (SELECT
                    FYStart
                    ,FYNextStart = DateAdd(yy,1,FYStart)
                    ,FYPrevStart = DateAdd(yy,-1,FYStart)
                FROM
                    (SELECT FYStart = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)))dt1
                  ) fy
WHERE
d.DateFirstIssued > fy.FYStart and d.DateFirstIssued < fy.FYNextStart

This will return all records with DateFirstIssued within the current fiscal year

Fiscal quarters

Calculating fiscal quarter is very similar to calculating fiscal year, here is the code to get each of the four quarters’ starting date:

    SELECT
            FYQ1
            ,FYQ2 = DateAdd(mm, 3, FYQ1)
            ,FYQ3 = DateAdd(mm, 6, FYQ1)
            ,FYQ4 = DateAdd(mm, 9, FYQ1)
      FROM
(SELECT FYQ1 = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) dt1

It returns:

FYQ1                          FYQ2                       FYQ3                       FYQ4
2013-11-01 00:00:00.000       2014-02-01 00:00:00.000    2014-05-01 00:00:00.000    2014-08-01 00:00:00.000

Code example:

SELECT * FROM dbo.Document  d
    CROSS JOIN (    SELECT
            FYQ1
            ,FYQ2 = DateAdd(mm, 3, FYQ1)
            ,FYQ3 = DateAdd(mm, 6, FYQ1)
            ,FYQ4 = DateAdd(mm, 9, FYQ1)
      FROM
(SELECT FYQ1 = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) dt1) fy
WHERE
d.DateFirstIssued > fy.FYQ2 and d.DateFirstIssued < fy.FYQ3

This query lists all records with DateFirstIssued in Fiscal year second quarter, that is: between 2014-02-01 00:00:00.000 and 2014-05-01 00:00:00.000

This article’s credit should mostly be given to Lynn Pettis and Jeff Moden on sqlservercentral, I hope it is simple and useful to someone with the need.