Financial Year - working out start date

SQL 2008

We currently have a financial year in the company I work for starting 1st May running through to 30th April
In the query I am developing I need to have a start date of the beginning of the financial year, 2 financial years ago

At the moment I add it in myself

i.e declare @start datetime = '01-May-14'
I want to add something that will automatically calculate the start date.

Can anybody help?
Thanks
Mark WilsonBI DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Been there, done that, and posted code you can download to build a calendar table to handle custom fiscal years --> SQL Server Calendar Table, then SQL Server Calendar Table:  Fiscal Years
Scott PletcherSenior DBACommented:
DECLARE @fy_start datetime

SET @fy_start = DATEADD(MONTH, 4, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 2 + CASE WHEN MONTH(GETDATE()) >= 5 THEN 1 ELSE 0 END, 0))

SELECT @fy_start


That is not nearly as complex as it looks.  The main calc is:
SELECT  DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
which is the "standard" efficient way to get Jan 1 of the current year.  Since we want May 1, the first part adds 4 months.  And, finally, since we need to go back two years, the "-2 ..." part does that adjustment.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocCommented:
I would do this:

declare
    @date datetime='2015-11-02'

select 
   cast(case when month(@date)>=5 and day(@date)>=1 then Year(@date) else Year(@date)-1 end as varchar(4))+'-05-01'

Open in new window


If the month of the date you check >=5 and the day>1 the fiscal year start is in the same year as your check date's year else is in the previous year.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott PletcherSenior DBACommented:
From a performance standpoint, anything will strings will run much more slowly.
ZberteocCommented:
The difference is insignificant. For 10000 dates:

Your method:
Client processing time               4.1667
Total execution time                27.3333
Wait time on server replies    23.1667

My method
Client processing time             6.2000
Total execution time              25.8000
Wait time on server replies  19.6000
grendel777Commented:
I'm a big proponent of creating a "date dimension" table like you find big data warehouses. They're immensely helpful for reporting. It's basically a big table with every date for whatever years you want to cover, and it can include fields like DayOfFiscalYear, DayOfCalendarYear, DayOfWeekName, FiscalQuarter, FirstDayOfMonth, IsWeekDay, etc. You can do a web search for "SQL generate date table" to get some code that will generate it for you: you'll need to tweak it to recognize 5/1 as the start of your fiscal year, but if you find some code you like and post it, we can help with that part.
Vitor MontalvãoMSSQL Senior EngineerCommented:
halifaxman, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.