Re: Date conversion using day of week

On 03/29/2011 08:50 AM, Adrian Klaver wrote:
> On Tuesday, March 29, 2011 8:33:59 am Steve Crawford wrote:
>> On 03/29/2011 08:07 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
>> Based on running the queries in 9.0, it's behavior that has been corrected:
>>
>> select to_date('Mon1-13-Tue', 'YYYY-IW-DY');
>> ERROR: invalid combination of date conventions
>> HINT: Do not mix Gregorian and ISO week date conventions in a
>> formatting template.
>>
>> Cheers,
>> Steve
>
> Yes and no:)
>
> 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-SUN', 'IYYY-IW-DY');
> to_date
> ------------
> 2011-03-28
> (1 row)
>
>
>

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