mister.magoo (1/6/2011)
Rem70Rem (1/6/2011)
This seems to be a good approach, but some year have 52 weeks when some other have 53.
The DATEPART function has a WEEK datepart that displays the week of the year. You should be able to use this to dislpay the weeks of the year, if you use it with some custom code to account for
(1) your financial year's week 1,
(2) your financial week's starting day (e.g., does the week always begin on a Sunday?), and
(3) which year has 53 weeks instead of 52. While DATEPART(wk,...) takes into account years with 53 weeks, you need to make sure that this aligns with your financial calendar.
Disregarding all of that for the moment, a simple example of finding the week number for any given date would be:
SELECT DATEPART(WK,GETDATE())
And, to find the difference in weeks between two dates:
SELECT DATEDIFF ( WK , '1-1-2010', GETDATE())
BOL for DatePart: http://msdn.microsoft.com/en-us/library/ms174420.aspx
BOL for DateDiff http://msdn.microsoft.com/en-us/library/ms189794.aspx
-Jen