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

[Conficker111111]

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.

[adam_k]

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.

[Conficker111111]

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.