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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq