By Raymond Xie,

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.

Total article views: 5973 | Views in the last 30 days: 80

Related Articles
ARTICLE

Learn how to use Dateadd/Datediff functions to manipulate dates in this short article from Seth Phel...

FORUM

select 21/(datediff(dd,getdate(),getdate())

select 21/(datediff(dd,getdate(),getdate())

FORUM

DATEDIFF WEEKS!!!

DATEDIFF WEEKS !!!

FORUM

BLOG

Flexible Fiscal Data Sort

Flexible Fiscal Data Sorting A few months ago, I faced the challenge of extracting data via SQL Que...

Tags
 fiscal quarter fiscal year t-sql

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platformâ€¦ And itâ€™s our huge, buzzing community of SQL Server Professionals that makes it such a success.