You should change the calculation to the ISO calendar:
TD_SYSFNLIB.WEEKNUMBER_OF_YEAR (CURRENT_DATE, 'ISO')
And you probably need the ISO-year, too:
In TD14 there's also Oracle's TO_CHAR:
Thanks Khurram and Dieter - But I am getting an error while running
| || |
It says - Cannot resolve column - TD_SYSFNLIB. Specify Table or View.
You can find the complete info about this function at this link, and can use the appropriate syntax.
Select mv.col1, mv.col2, c.week_of_year from MyView mv inner join sys_calendar.calendar c on mv.my_dat= c.calendar_date
Join to the sys_calendar.calendar view on your_date = calendar_date and pull the week_of_year column from the system calendar.
when you want 1st of january to be in week 1, both sys_calendar and iso calendar will fail.
Can you elaborate about the rules for this calendar?
When does a week start, sunday or monday?
What about the last days of a year, does 2013-12-31 belong to week 1 of 2014 or week 52 of 2013?
Thank you all for your help with this. So Jan 1 2014, will always belong to week 1 and then subsequent weeks will follow. Week starts on a Sunday. Does it make sense?