Re: Postgres 7.3 migrate to 8.0 date problems.

On Mon, 2005-03-28 at 15:48, Scott Marlowe wrote:
> On Mon, 2005-03-28 at 13:44, Thomas Seeber wrote:
> > Hi,
> > 
> > We were upgrading from postgres 7.3 -> 8.0 and having a little
> > problems importing dates from some of our data sources.  Say we have a
> > date like '2004-17-05'.  In postgres 7.3, postgres would intrept this
> > as Year Day Month automatically.  In the documentation, from postgres
> > 7.4 on this has to be specified in the Datestyle option and YDM is not
> > an option.  Other data we have is coming in on the YMD formate which
> > would be more expected.  I realize that this change is better for data
> > integrity, however we have alot of legacy systems where being able to
> > mimic the 7.3 behaviour would be desireable.  Any ideas?
> 
> Fix the data?  I had to write a lot of scripts to keep dates like that
> OUT of my last PostgreSQL installation, which was running 7.2  Which is
> why, as the guy who whinged and moaned until this behavioural change was
> made, I feel for you, but honestly, the lackadaisical manner of handing
> that particular format (YYYY-DD-MM) never really seemed right even to
> the people who fought me on the idea of changing the default behaviour
> of DD/MM/YYYY versus MM/DD/YYYY.  
> 
> While the US uses MM/DD/YYYY and Europe uses DD/MM/YYYY, and there may
> be some arguments for handling a sloppy version of one of those,
> computer folk (and the government) who want easily ordered dates use
> YYYY-MM-DD, I've never seen a good argument made for the usage of
> YYYY-DD-MM before.
> 
> Are you sure that the other dates in your data set are what you think
> they are?  Because if the two numbers are both <=12, then you'll get one
> "date" and if the wrong one is >12 you'll get another.  That can't be
> good.  
> 

Would it be possible to use a BEFORE trigger to reformat the YYYY-DD-MM
date to YYYY-MM-DD ?  The error I see on 7.4 is ERROR:  date/time field
value out of range: "2005-14-01" so ISTM you could do some data
manipulation if you wanted.


Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL