Thread

Re: 7.1 euro-style dates insert error

Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-05-03T05:28:03Z

> > Yes it does, for ambiguous cases such as yours.
> Which means that independent of the date style, it should give a date error
> either way?

No, it means that for ambiguous cases (e.g. '2-10-1997') it will assume
European or US conventions were used. It will rarely reject a date on
grounds of ambiguity, since common usage in many countries is guaranteed
to be ambiguous. That is why ISO and four digit years are to be
preferred.

> > I'm willing to bet that the date style is *not* set to "European".
> > Please demonstrate with a "show datestyle" and "select date
> > '2.10.1997'"...
> NOTICE: DateStyle is ISO with European conventions.
> ?column?
> -----------
> 1997-10-02

OK, so this is a correct result...

> Seems to be a problem with inserting reversed dates (Eg. 1997.13.2) and
> invalid dates...
> Inserting 10.13.1997:
>     gives 'Bad external date representation 10.13.1997'        -    correct

Hmm, I would have thought that this would be interpreted as mm.dd.yyyy
for sure, but instead it is enforcing the "european ordering" of the
fields. If you switch to "US" style, the date is accepted. That is OK I
think...

> Inserting '19.13.2':
>     gives '2013-02-19'    (dd.yy.mm ??? )

What would you want this interpreted as? dd.mm.y? Postgres allows years
back to 4213BC, so a one digit year might be accepted indicating a time
two millennia ago.

The algorithm for interpreting dates is in an appendix in the User's
Guide. Does this behavior match your reading of that writeup? Not that
this would make it acceptable, but at least it would be working as
advertised ;)

In this case, it seems to give up right away on an ISO date since it has
only a two digit leading field. It tries that as a day, since it can not
possibly be a month (too big). The next field then gets picked up as the
year, since it cannot possibly be a month (too big). Then the last field
is picked up as a month, since that is the only thing left.

> Unfortunately I am inserting 20,000 dates into a table, so it is not a one
> off case.
> Is there any way to enforce specific date formats without the parser
> calculating the 'best-fit' case?
> > > If the dates are entered as 'ccyy.mm.dd' it is okay - unfortunately
> > > all my dates are in the format 'dd.mm.ccyy'.

You had indicated that all of your dates were in a specific format with
four digit years. Are you saying now that they should be, but that some
of the inputs are invalid? Or are they a mix of every possibility, and
you want to reject those with some properties but not others?

If so, you might try using to_date() to enforce a specific input format.
You might find it easier to ingest these into a text column first, then
manipulate from there (for example, you could prepend the century
digits). But what do you want to do with the invalid entries? Is it OK
to ignore them??

                           - Thomas