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



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.

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.

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.


Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.