Re: Date conversion using day of week

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(dot)klaver(at)gmail(dot)com