Could anyone please assist me in getting given date's week (in current year, and not relative to any perticular date).
E.g. If I pass today's date as 20/11/2012 (20-November-2012).
It should return the week number 47.
Week number based on which definition?
This is a SQL function for TD13.10 to calculate it based on the International Standard, i.e. week starts on monday and the first week always includes jan 4.
REPLACE FUNCTION iso_week(cdate DATE)
RETURNS NULL ON NULL INPUT
INLINE TYPE 1
(((cDate - (((cdate - DATE '0001-01-01') MOD 7) + 1) + 4)
- ((EXTRACT(YEAR FROM (cDate - (((cdate - DATE '0001-01-01') MOD 7) + 1) + 4)) - 1900) * 10000 + 0101 (DATE))) / 7) + 1 (FORMAT '99')
Otherwise you might join to sys_calendar.calendar or check how it's calculating it.