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