Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Scott
Posting Yak Master
145 Posts |
 Posted - 2003-05-15 : 09:34:21
|
| Financial year is 1 April till end March.Given 2 dates I need to work out how many financial years they span.eg:declare @start smalldatetime, @end smalldatetimeselect @start = '20030101', @end = '20081231'Select datepart(year, @end) - datepart(year, @start)This gives 5 but correct answer is 7. |
|
|
Scott
Posting Yak Master
145 Posts |
Posted - 2003-05-15 : 09:43:43
|
| Got it:Select case when datepart(qq, @end) < 2 then datepart(year, @end) -1 else datepart(year, @end) +1 End- case when datepart(qq, @start) < 2 then datepart(year, @start) -1 else datepart(year, @start) +1 endNot quite, some exceptions eg:20010401 - 20020331, still looking.Edited by - scott on 05/15/2003 10:08:34 |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-16 : 19:33:05
|
| This should do it:SELECT DateDiff(year, DateAdd(month, -3, @start), DateAdd(month, -3, @end))Basically you're using DateAdd to apply an offset value so that the calculations are made from a new baseline; April 1 instead of January 1.Edited by - robvolk on 05/16/2003 19:34:10 |
|
|
|
|
|
|
|
|
Financial Year Dateparts