Skip site navigation (1) Skip section navigation (2)

Re: BUG #4372: TO_DATE with ISO week and day

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Kees Westerlaken <kees(dot)westerlaken(at)valuecare(dot)nl>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4372: TO_DATE with ISO week and day
Date: 2008-08-22 16:16:04
Message-ID: 200808221616.m7MGG4r21692@momjian.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Kees Westerlaken wrote:
> 
> The following bug has been logged online:
> 
> Bug reference:      4372
> Logged by:          Kees Westerlaken
> Email address:      kees(dot)westerlaken(at)valuecare(dot)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(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

In response to

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2008-08-22 16:52:51
Subject: Re: BUG #4274: uuid returns duplicate values
Previous:From: Tom LaneDate: 2008-08-22 15:47:49
Subject: Re: range value problem with double precision [PG 8.3.3]

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group