On Tuesday, March 29, 2011 9:02:52 am Steve Crawford wrote: > > But you changed it to specify an ISO year avoiding the mixed > conventions. According to the 9.0 docs > (http://www.postgresql.org/docs/9.0/static/functions-formatting.html): > > "An ISO week date (as distinct from a Gregorian date) can be specified > to to_timestamp and to_date in one of two ways: > > Year, week, and weekday: for example to_date('2006-42-4', > 'IYYY-IW-ID') returns the date 2006-10-19. If you omit the weekday it is > assumed to be 1 (Monday). > > Year and day of year: for example to_date('2006-291', > 'IYYY-IDDD') also returns 2006-10-19. > > Attempting to construct a date using a mixture of ISO week and Gregorian > date fields is nonsensical, and will cause an error. In the context of > an ISO year, the concept of a "month" or "day of month" has no meaning. > In the context of a Gregorian year, the ISO week has no meaning. Users > should avoid mixing Gregorian and ISO date specifications. " > > So I guess the upshot is that 9.0 throws errors on mixed input, but the > OP's issues can probably be resolved by explicitly specifying an ISO > year in the formatting. > > Cheers, > Steve Well the strange part is only fails for SUN: test(5432)aklaver=>select to_date('2011-13-MON', 'IYYY-IW-DY'); to_date ------------ 2011-03-28 (1 row) test(5432)aklaver=>select to_date('2011-13-TUE', 'IYYY-IW-DY'); to_date ------------ 2011-03-29 (1 row) test(5432)aklaver=>select to_date('2011-13-WED', 'IYYY-IW-DY'); to_date ------------ 2011-03-30 (1 row) test(5432)aklaver=>select to_date('2011-13-THU', 'IYYY-IW-DY'); to_date ------------ 2011-03-31 (1 row) test(5432)aklaver=>select to_date('2011-13-FRI', 'IYYY-IW-DY'); to_date ------------ 2011-04-01 (1 row) test(5432)aklaver=>select to_date('2011-13-SAT', 'IYYY-IW-DY'); to_date ------------ 2011-04-02 (1 row) test(5432)aklaver=>select to_date('2011-13-SUN', 'IYYY-IW-DY'); to_date ------------ 2011-03-28 Agreed, maintaining ISO arguments across the board is the way to go: Monday select to_date('2011-13-1', 'IYYY-IW-ID'); to_date ------------ 2011-03-28 Sunday select to_date('2011-13-7', 'IYYY-IW-ID'); to_date ------------ 2011-04-03 -- Adrian Klaver adrian.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
