Get current week number from the date in current year.

Database

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 !!

3 REPLIES
Junior Contributor

Re: Get current week number from the date in current year.

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

Enthusiast

Re: Get current week number from the date in current year.

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;

Re: Get current week number from the date in current year.

Thanks a lot; will check on this !!