Kees Westerlaken wrote:
>
> The following bug has been logged online:
>
> Bug reference: 4372
> Logged by: Kees Westerlaken
> Email address: kees.westerlaken@valuecare.nl
> PostgreSQL version: 8.3.3
> Operating system: Windows
> Description: TO_DATE with ISO week and day
> Details:
>
> The TO_DATE conversion with ISO week (IW) and ISO day (ID) format accepts
> invalid values and sometimes returns wrong result.
>
> select to_date('2008-31-4','IYYY-IW-ID')
> results in "2008-07-31", which is correct
>
> The valid values for IW are 1-7, however:
I think you mean "valid values for ID".
> select to_date('2008-31-8','IYYY-IW-ID')
> results in "2008-08-04" of which you could argue if it is correct.
It is true we don't error out as often as Oracle for to_date(). We have
a TODO item for this:
Fix to_date()-related functions to consistently issue errors
* Invalid to_date patterns (was: [PATCHES] [GENERAL] ISO week dates)
> However:
> select to_date('2008-31-10','IYYY-IW-ID')
> results in "2008-07-28", which is not correct
Well, as you said ID=10 isn't a valid value, so the code defaults to
Monday, but it seems it goes to the next week until Tuesday, then wraps
back, which is odd.
> And even worse: if you use the IW and ID format in an older version it
> works, but the result is wrong.
> select to_date('2008-31-4','IYYY-IW-ID')
> in version 8.2.6 results in "2004-07-26", which is wrong.
Yes, we have fixed some of this in 8.3.
I have added your report to the existing TODO item.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +