Dynamic SQL for current financial year dates only

Question

I have a database which contains two dates which I am concerned with.

StartDate + EndDate

These dates are stored in the following format:

2008-06-23 00:00:00.000

I need to add a piece of dynamic SQL to only bring back dates which fall in the current financial year.

so for example 01/04/2011 - 31/03/2012 would be this financial year.

Therfore any record whos enddate is within these dates, or is NULL is classed as 'active'

The year part will need to be dynamic so that as soon as it hits 1st April 2012 we move into the new financial year and will be bringing data back from 01/04/2012 - 31/03/13 and so on.

Can suggest some code or point out any rules I have overlooked?

Answer 1

Try:

...
where StartDate >= 
    case 
        when month(getdate()) > 3 
            then convert(datetime, cast(year(getdate()) as varchar) + '-4-1')
        else 
            convert(datetime, cast(year(getdate()) - 1 as varchar) + '-4-1')
    end 
and (EndDate is null or EndDate < 
    case 
        when month(getdate()) > 3 
            then convert(datetime, cast(year(getdate()) + 1 as varchar) + '-4-1')
        else 
            convert(datetime, cast(year(getdate()) as varchar) + '-4-1')
    end)

Answer 2

Here we go :)

Dynamic solution,

DECLARE @MyDate DATETIME
SET @MyDate = getDate()

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = DATEADD(dd,0, DATEDIFF(dd,0, DATEADD( mm, -(((12 + DATEPART(m, @MyDate)) - 4)%12), @MyDate ) - datePart(d,DATEADD( mm, -(((12 + DATEPART(m, @MyDate)) - 4)%12),@MyDate ))+1 ) )
SET @EndDate = DATEADD(ss,-1,DATEADD(mm,12,@StartDate ))

SELECT @StartDate,@EndDate

Answer 3

Create a Finacial_Year lookup table containing the fields financial_year_start and financial_year_end.

Populate it with the next 50 years of data (easy to do using a spread sheet to calulate the dates).

Join your table to the lookup table using enddate

SELECT ...
FROM Your_Table LEFT JOIN Financial_Year
ON Your_Table.enddate BETWEEN Financial_Year.financial_year_start
    AND Financial_Year.financial_year_end
WHERE Your_Table.enddate IS NULL -- Active 
    OR (getdate() BETWEEN Financial_Year.financial_year_start
    AND Financial_Year.financial_year_end)

The current financial will change automatically when the current date falls between the next two dates.

BTW the UK financial year runs from 06-04-YYYY to 05-04-YYYY, not the 1st to the 31st.