On Mon, Sep 30, 2002 at 06:31:15PM -0400, Tom Lane wrote: > The middle part of that boils down (as of today) to > > regression=# select to_date('402002', 'WWYYYY'); > to_date > ------------ > 2002-10-01 > (1 row) > > and Oct 1 (tomorrow) is Tuesday. As to why it picks that day to > represent Week 40 of 2002, it's probably related to the fact that Week 1 > of 2002 is converted to > > regression=# select to_date('012002', 'WWYYYY'); > to_date > ------------ > 2002-01-01 > (1 row) > > which was a Tuesday. > > Offhand this seems kinda inconsistent to me --- I'd expect > > regression=# select extract(week from date '2002-09-30'); > date_part > ----------- > 40 > (1 row) > > to produce 39, not 40, on the grounds that the first day of Week 40 > is tomorrow not today. Alternatively, if today is the first day of > Week 40 (as EXTRACT(week) seems to think), then ISTM that the to_date > expression should produce today not tomorrow. > > I notice that 2001-12-31 is considered part of the first week of 2002, > which is also pretty surprising: > > regression=# select extract(week from date '2001-12-31'); > date_part > ----------- > 1 > (1 row) > > > Anyone able to check this stuff on Oracle? What exactly are the > boundary points for EXTRACT(week), and does to_date() agree?
Please, read docs -- to_() functions know two versions of "number of week" IW = iso-week WW = "oracle" week test=# select to_date('402002', 'WWYYYY'); to_date ------------ 2002-10-01 (1 row) test=# select to_date('402002', 'IWYYYY'); to_date ------------ 2002-09-30 (1 row) test=# select to_date('012002', 'WWYYYY'); to_date ------------ 2002-01-01 (1 row) test=# select to_date('012002', 'IWYYYY'); to_date ------------ 2001-12-31 (1 row) Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]