Given the date of the first day of a fiscal year, how do we compute the fiscal week of any day in the year?
If the fiscal year begins on day @d of month @m, the fiscal week of date @d is:
Floor( DateDiff( @d,
Date(Concat( Year(@d)-If(Month(@d)*100+Day(@d)<@m*100+@d,1,0),
'-',
@m,
'-',
@d
)
)
) / 7
) + 1 AS 'Fiscal week of 1 Jul 2010';
which for our example is Floor( DateDiff( '2010-7-1', '2010-4-6') / 7 ) + 1 = 13 .Last updated 30 Sep 2010 |
 |