All Forums Database
tejasmore 3 posts Joined 11/12
20 Nov 2012
Get current week number from the date in current year.

Hi,
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.
 
Thanks !!

dnoeth 4628 posts Joined 11/04
20 Nov 2012

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 INT
SPECIFIC iso_week_date
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN
  (((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.
Dieter

Dieter

whitestorm2k 8 posts Joined 09/04
20 Nov 2012

 

Hello,

 

I guess that: the 'week_of_year' in 'sys_calendar.calendar' table is what you want.

ex:

sel week_of_year

from sys_calendar.calendar

where calendar_date=date;

tejasmore 3 posts Joined 11/12
20 Nov 2012

Thanks a lot; will check on this !!

You must sign in to leave a comment.