Calculate Fiscal Year week number based on a date
731736Nov 3 2009 — edited Nov 3 2009Hello all.
I want to build a sql query which return week number of a concrete date.
The problem I have, it's I'm not working with natural weeks, I'm working with fiscal year weeks. That's the reason because I can't use a function like... WEEK(date).
I use TO_CHAR(R.PRODUCTIONDAY, 'IW') to get week based on date, I put in this post WEEK(X) just to put it briefly.
In my case, the Fiscal Year in my company begins 1st of October.
Depending on this year calendar is:
01/10/2009 -->1 (In this case, the week has less than 7 days. It begins on Thursday 1st)
...
04/10/2009 -->1
05/10/2009 -->2
...
11/10/2009 -->2
12/10/2009 -->3
...
18/10/2009 -->3
19/10/2009 -->4
...
25/10/2009 -->4
26/10/2009 -->5
...
01/11/2009 -->5
02/11/2009 -->6
...
08/11/2009 -->6
I've been trying adding days/months to the date WEEK(date + X) trying to build date according to natural year, to use a function like WEEK() but I haven't found the exact formula.
Is there a tested method to calculate this???
Thank you very much.