Practical Business Intelligence

T-SQL provides very useful date management functions such as DATEPART, DATEADD, DATEDIFF and DATENAME. With these functions it's easy to find things out about any given date. For example, to get the name of the month you just need to do:

select datename(m, '2009-05-06')

To get the first date of this year you can use:

select dateadd(yyyy, datediff(yyyy, 0, '2009-05-06'), 0)

This only works for calendar (Jan - Dec) years, though, because SQL Server has no innate understanding of fiscal years in which the first day of the "year" is not Jan 1st.

Many companies have a fiscal or financial year beginning in a different month. In the UK, the tax year starts 1st April and many companies align with that. So what fiscal year does a date fall into and how can this be determined in SQL? This might be particularly important if you have to build a date dimension, and for every date in it you want to have columns for calendar periods and fiscal periods.

The algorithm for date is as follows:

if the calendar date is before the start of the fiscal year, its year began on the first day of the previous fiscal year; if it is on or after the start of the fiscal year, its year began on the first day of the next fiscal year.

This is easiest to imagine with an example. If your fiscal year starts 1st April, all dates in 2009 from January to March belong to the year that started 1st April 2008 and all dates from April to December belong to the year that starts 1st April 2009.

A simple SQL script that can deliver this is:

declare @d datetime
set @d = '2009-03-10'  -- 10th March 2009

select
DATEADD(yyyy, DATEDIFF(yyyy, 0, @d), 0) as [CalendarYear]
,case
when DATEPART(M, @d) >= 4 then DATEADD(mm, 3, DATEADD(yyyy, DATEDIFF(yyyy, 0, @d), 0))
when DATEPART(M, @d) < 4 then DATEADD(mm, -9, DATEADD(yyyy, DATEDIFF(yyyy, 0, @d), 0))
end as [FiscalYear]

What's it doing? It determines if the date is before or after 1st April. If it's after, then the start date of the fiscal year is three months after the start of the calendar year. If it's before, then the start date for the fiscal year is nine months before the start of the calendar year.

Once you have got the correct fiscal year start date, you can easily work out fiscal quarter, month number, week number, day number by using DATEDIFF between the start date and your date. This code will work fine in a stored procedure that creates data for a date dimension (i.e. with a CTF or loop), or just to return periods for a single date.