On Mon, Sep 30, 2002 at 05:37:47PM -0400, Rod Taylor wrote: > select to_char( > to_date( > CAST(extract(week from CURRENT_TIMESTAMP) as text) > || CAST(extract(year from CURRENT_TIMESTAMP) as text) > , 'WWYYYY') > , 'FMDay, D'); > > to_char > ------------ > Tuesday, 3 > (1 row) >
The PostgreSQL not loves Thuesday, but WW for year 2002 loves it. Why? Because 'WW' = (day_of_year - 1) / 7 + 1, other words this year start on Thuesday (see 01-JAN-2002) and WW start weeks each 7 days after this first day of year. If you need "human" week you must use IW (iso-week) that start every Monday. I know there're countries where week start on Sunday, but it's not supported -- the problem is with 'D' it returns day-of-week for Sunday-based-week. Your example (I use to_xxx () only, it's more readable): If you need correct for Sunday-based-week: select to_char( to_date(to_char(now(), 'IWYYYY'), 'IWYYYY')-'1d'::interval, 'FMDay, D'); to_char ----------- Sunday, 1 If you need Monday-based-week (ISO week): test=# select to_char( to_date(to_char(now(), 'IWYYYY'), 'IWYYYY'), 'FMDay, D'); to_char ----------- Monday, 2 '2' is problem -- maybe add to to_xxx() functions 'ID' as day-of-isoweek. It's really small change I think we can do it for 7.3 too. What think about it our Toms? In the Oracle it's same (means WW vs. IW vs. D) SVRMGR> select to_char(to_date('30-SEP-02'), 'WW IW Day D') from dual; TO_CHAR(TO_DATE(' ----------------- 39 40 Monday 2 test=# select to_char('30-SEP-02'::date, 'WW IW Day D'); to_char ------------------- 39 40 Monday 2 SVRMGR> select to_char(to_date('29-SEP-02'), 'WW IW Day D') from dual; TO_CHAR(TO_DATE(' ----------------- 39 39 Sunday 1 test=# select to_char('29-SEP-02'::date, 'WW IW Day D'); to_char ------------------- 39 39 Sunday 1 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html