1,105,456 Community Members

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

Member Avatar
Conficker111111
Newbie Poster
3 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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.

Member Avatar
adam_k
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]
 
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.

Member Avatar
Conficker111111
Newbie Poster
3 posts since Feb 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
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 three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article