Re: Postgres 7.3 migrate to 8.0 date problems.

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.