|
On Tuesday, March 29, 2011 8:07:48 am Marc Munro wrote: > I'm trying to validate a day of the week, and thought that to_date would > do the job for me. But I found a case where it cannot tell the > difference between sunday and monday. Is this a bug or intended > behaviour? > > dev=# select to_date('2011-13-Mon', 'YYYY-IW-DY'); > to_date > ------------ > 2011-03-28 > (1 row) > > dev=# select to_date('2011-13-Sun', 'YYYY-IW-DY'); > to_date > ------------ > 2011-03-28 > (1 row) > > dev=# select to_date('2011-13-Tue', 'YYYY-IW-DY'); > to_date > ------------ > 2011-03-29 > (1 row) > > This is on postgres 8.3.14. > > __ > Marc Well in 9.0.3 this raises an error: select to_date('2011-13-Sun', 'YYYY-IW-DY'); ERROR: invalid combination of date conventions HINT: Do not mix Gregorian and ISO week date conventions in a formatting template From the docs: http://www.postgresql.org/docs/9.0/interactive/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:
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 try this: 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 |
