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:
- 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 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@gmail.com