Member Avatar for 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.

Edited by Conficker111111 because: n/a
  • 2 Contributors
  • 2 Replies
  • 1K Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Conficker111111

Recommended Answers

All 2 Replies

Member Avatar for 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.

Member Avatar for 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.

Be a part of the DaniWeb community

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