,

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)`

`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)`

`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
FROM

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
FROM
) 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
FROM

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
FROM
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.

Rate

3.93 (15)

You rated this post out of 5. Change rating

Rate

3.93 (15)

You rated this post out of 5. Change rating