1.11M Members

Dynamic Query to get Fiscal(Financial Year) SQL Server 2005

 
0
 

Below dynamic query will set StartDate and EndDate to 1st of March to 30th April as UK financial year.

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

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

SET @EndDate = DATEADD(SS,-1,DATEADD(mm,12,@StartDate))


SELECT @StartDate,@EndDate

Note: to get different dates you can play around StartDate query to get different dates based on your requirements.

 
0
 

2 things: March 1st to April 30th is a 2 month period, if that's the UK financial year what happens to the rest 10 months? I'm guessing this is a typo, as your code proves.
second thing is why not just set dateformat and set the date as a string?

set @StartDate = convert(datetime,year(getdate()) + '-03-01')

PS: Please use the CODE tags around your code. Thanks.

 
0
 

Hi adam_k,

Thank you for you reply and suggestions.

1) The Financial year in UK is March to April, that means(current financial year) 01/03/2011 to 31/04/2012, so it's not only two months. for next year it will be 01/03/2012 to 31/04/2013. if you run above script you will get idea.

2) Both dates are in datetime datatype, so if you want to display in different format you can use Convert() function.

Thanks.

You
This article has been dead for over six months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article