Re: Date conversion using day of week

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