Re: Dutch week numbers

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 08 Jul 1999 20:53:31 GMT
Message-ID: <378a0dd2.29699345@inet16.us.oracle.com>

On Thu, 08 Jul 1999 20:31:50 GMT, jantah_at_big.hand.com (Jantah) wrote:

>I am looking for an easy way to 'calculate' Dutch week numbers. Over
>here, week 1 is the first week with at least 4 days in january.
>According to this logic, december 29 can be week 1 of the next year.
>Also, january 3 can be week 52 or 53 of the previous year.
>
>As far as I can see, the TO_CHAR(blahblah, 'WW') function can not do
>this. As I am going to need the function in some quite large queries,
>I need some sleek peace of PL/SQL.
>
>Now, it wouldn't be too hard to use the TO_CHAR for normal weeks, and
>include some code that also takes the exceptions into account, but I
>hate to invent the wheel. Does anybody have a complete procedure (or
>better still, some native Oracle solution) for me?

I think the 'IW' format mask is what you want.

SQL> select '01-jan-1999' the_date,
  2 to_char( to_date('01-jan-1999'), 'IW' ) iw   3 from dual
  4 /

THE_DATE IW
----------- --
01-jan-1999 53

SQL> select '02-jan-1999' the_date,
  2 to_char( to_date('02-jan-1999'), 'IW' ) iw   3 from dual
  4 /

THE_DATE IW
----------- --
02-jan-1999 53

SQL> select '03-jan-1999' the_date,
  2 to_char( to_date('03-jan-1999'), 'IW' ) iw   3 from dual
  4 /

THE_DATE IW
----------- --
03-jan-1999 53

SQL> select '04-jan-1999' the_date,
  2 to_char( to_date('04-jan-1999'), 'IW' ) iw   3 from dual
  4 /

THE_DATE IW
----------- --
04-jan-1999 01

hope this helps.

chris.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jul 08 1999 - 15:53:31 CDT