Fiscal week of a date

from the Artful MySQL Tips List

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

Return to the Artful MySQL Tips page