We all know what the 'ww' date format element does, right? It provides the week number such that January 1 is always in week 1. The 'iw' element is a little different in that January 1 can be week 53 of the previous year if it is a Friday, Saturday or Sunday.
Today, my boss - who has worked with Oracle for 18 years now - was surprised to see that Monday, January 28 2008 is week 4 and Tuesday, January 29 2008 is week 5. He asked me to have a look and I was surprised too. My boss and me both seem to have made the same false assumption for all these years. It is the assumption that a week runs from Monday until Sunday, and that all days in between have the same week number. In our assumption week 1 of 2008 contains 6 days from Tuesday January 1 2008 until Sunday January 6 2008. And week 2 would again be "normal" and runs from Monday January 7 2008 and Sunday January 13 2008.
So I ran a few queries like this to verify this assumption:
rwijk@ORA11G> alter session set nls_date_language = 'american'
2 /
Sessie is gewijzigd.
rwijk@ORA11G> select to_char
2 ( date '2003-12-20' + level
3 , 'day yyyy-mm-dd yyyy ww iyyy iw'
4 )
5 from dual
6 connect by level <= 42
7 /
TO_CHAR(DATE'2003-12-20'+LEVEL,'DAYYYY
--------------------------------------
sunday 2003-12-21 2003 51 2003 51
monday 2003-12-22 2003 51 2003 52
tuesday 2003-12-23 2003 51 2003 52
wednesday 2003-12-24 2003 52 2003 52
thursday 2003-12-25 2003 52 2003 52
friday 2003-12-26 2003 52 2003 52
saturday 2003-12-27 2003 52 2003 52
sunday 2003-12-28 2003 52 2003 52
monday 2003-12-29 2003 52 2004 01
tuesday 2003-12-30 2003 52 2004 01
wednesday 2003-12-31 2003 53 2004 01
thursday 2004-01-01 2004 01 2004 01
friday 2004-01-02 2004 01 2004 01
saturday 2004-01-03 2004 01 2004 01
sunday 2004-01-04 2004 01 2004 01
monday 2004-01-05 2004 01 2004 02
tuesday 2004-01-06 2004 01 2004 02
wednesday 2004-01-07 2004 01 2004 02
thursday 2004-01-08 2004 02 2004 02
friday 2004-01-09 2004 02 2004 02
saturday 2004-01-10 2004 02 2004 02
sunday 2004-01-11 2004 02 2004 02
monday 2004-01-12 2004 02 2004 03
tuesday 2004-01-13 2004 02 2004 03
wednesday 2004-01-14 2004 02 2004 03
thursday 2004-01-15 2004 03 2004 03
friday 2004-01-16 2004 03 2004 03
saturday 2004-01-17 2004 03 2004 03
sunday 2004-01-18 2004 03 2004 03
monday 2004-01-19 2004 03 2004 04
tuesday 2004-01-20 2004 03 2004 04
wednesday 2004-01-21 2004 03 2004 04
thursday 2004-01-22 2004 04 2004 04
friday 2004-01-23 2004 04 2004 04
saturday 2004-01-24 2004 04 2004 04
sunday 2004-01-25 2004 04 2004 04
monday 2004-01-26 2004 04 2004 05
tuesday 2004-01-27 2004 04 2004 05
wednesday 2004-01-28 2004 04 2004 05
thursday 2004-01-29 2004 05 2004 05
friday 2004-01-30 2004 05 2004 05
saturday 2004-01-31 2004 05 2004 05
42 rijen zijn geselecteerd.
rwijk@ORA11G> select to_char
2 ( date '2007-12-20' + level
3 , 'day yyyy-mm-dd yyyy ww iyyy iw'
4 )
5 from dual
6 connect by level <= 42
7 /
TO_CHAR(DATE'2007-12-20'+LEVEL,'DAYYYY
--------------------------------------
friday 2007-12-21 2007 51 2007 51
saturday 2007-12-22 2007 51 2007 51
sunday 2007-12-23 2007 51 2007 51
monday 2007-12-24 2007 52 2007 52
tuesday 2007-12-25 2007 52 2007 52
wednesday 2007-12-26 2007 52 2007 52
thursday 2007-12-27 2007 52 2007 52
friday 2007-12-28 2007 52 2007 52
saturday 2007-12-29 2007 52 2007 52
sunday 2007-12-30 2007 52 2007 52
monday 2007-12-31 2007 53 2008 01
tuesday 2008-01-01 2008 01 2008 01
wednesday 2008-01-02 2008 01 2008 01
thursday 2008-01-03 2008 01 2008 01
friday 2008-01-04 2008 01 2008 01
saturday 2008-01-05 2008 01 2008 01
sunday 2008-01-06 2008 01 2008 01
monday 2008-01-07 2008 01 2008 02
tuesday 2008-01-08 2008 02 2008 02
wednesday 2008-01-09 2008 02 2008 02
thursday 2008-01-10 2008 02 2008 02
friday 2008-01-11 2008 02 2008 02
saturday 2008-01-12 2008 02 2008 02
sunday 2008-01-13 2008 02 2008 02
monday 2008-01-14 2008 02 2008 03
tuesday 2008-01-15 2008 03 2008 03
wednesday 2008-01-16 2008 03 2008 03
thursday 2008-01-17 2008 03 2008 03
friday 2008-01-18 2008 03 2008 03
saturday 2008-01-19 2008 03 2008 03
sunday 2008-01-20 2008 03 2008 03
monday 2008-01-21 2008 03 2008 04
tuesday 2008-01-22 2008 04 2008 04
wednesday 2008-01-23 2008 04 2008 04
thursday 2008-01-24 2008 04 2008 04
friday 2008-01-25 2008 04 2008 04
saturday 2008-01-26 2008 04 2008 04
sunday 2008-01-27 2008 04 2008 04
monday 2008-01-28 2008 04 2008 05
tuesday 2008-01-29 2008 05 2008 05
wednesday 2008-01-30 2008 05 2008 05
thursday 2008-01-31 2008 05 2008 05
42 rijen zijn geselecteerd.
So week 1, according to the 'ww' date format element, always runs from January 1 until January 7. And in 2004, a week ran from Thursday until Wednesday and in 2008 it runs from Tuesday until Monday.
As said, this was not what I expected. Also, in 2007, where January 1 was a Monday, the behaviour described above was hidden.
Yet, the documentation is very clear about this subject, as can be seen by these quotes:
The week numbers returned by the WW format mask are calculated according to the following algorithm: int(dayOfYear+6)/7. This algorithm does not follow the ISO standard (2015, 1992-06-15).
WW No Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
Which again shows that in our business one should never assume, but always read the documentation and verify by testing.
I'm trying to come up with an Oracle function do give me the PROPER first day of the week as per your article. I know how to do it in sql server but haven't a clue in oracle.
ReplyDeletehere's the sql server function passing in week 52 and year 2008 and returning 12/21/2008
Mikey,
ReplyDeleteThe answer depends on how you define your weeks: according to Oracle's WW-format, or ISO-weeks, or anything else?
And it depends on your definition of "first day of the week". This usually is sunday or monday, but it differs per country.
A random shot:
rwijk@ORA11GR1> var YEAR number
rwijk@ORA11GR1> var WEEK number
rwijk@ORA11GR1> exec :YEAR := 2008; :WEEK := 52
PL/SQL-procedure is geslaagd.
rwijk@ORA11GR1> select to_date(:YEAR,'yyyy')
2 + numtodsinterval( 7*:WEEK - 8, 'day')
3 from dual
4 /
TO_DATE(:YEAR,'YYYY
-------------------
22-12-2008 00:00:00
1 rij is geselecteerd.
By the way, the best place to ask this kind of question is OTN's SQL and PL/SQL forum.
Regards,
Rob.
Hi Rob,
ReplyDeletethats a great blog. Thanks for publish.
Cheers
Asterios