Thread: two digit years in inserts

I have this problem, I wrote a script to insert data into a table, one
of the columns is a birthdate now I only have the last two year digits,
and this all worked fine. Until I did a portupgrade (i am running on bsd
5.2.1 and 4.8 and postgresql 7.3.6 & 7.3.7 respectively) script worked
fine before the upgrade, now however, any year before 70 gets inserted
as 20xx and not 19xx.

My question is between which versions did this behaviour change, and is
there any way for me to force the old behaviour?

Regards
Justin Wyer

Justin Wyer <justin@isogo.co.za> writes:
> I have this problem, I wrote a script to insert data into a table, one
> of the columns is a birthdate now I only have the last two year digits,
> and this all worked fine. Until I did a portupgrade (i am running on bsd
> 5.2.1 and 4.8 and postgresql 7.3.6 & 7.3.7 respectively) script worked
> fine before the upgrade, now however, any year before 70 gets inserted
> as 20xx and not 19xx.

> My question is between which versions did this behaviour change, and is
> there any way for me to force the old behaviour?

Don't you think it's time to fix your data?  2-digit years were a bad
idea from the get-go, but it was possible to get away with it for awhile
near the end of a century.

            regards, tom lane

Tom Lane wrote:

>Justin Wyer <justin@isogo.co.za> writes:
>
>
>>I have this problem, I wrote a script to insert data into a table, one
>>of the columns is a birthdate now I only have the last two year digits,
>>and this all worked fine. Until I did a portupgrade (i am running on bsd
>>5.2.1 and 4.8 and postgresql 7.3.6 & 7.3.7 respectively) script worked
>>fine before the upgrade, now however, any year before 70 gets inserted
>>as 20xx and not 19xx.
>>
>>
>
>
>
>>My question is between which versions did this behaviour change, and is
>>there any way for me to force the old behaviour?
>>
>>
>
>Don't you think it's time to fix your data?  2-digit years were a bad
>idea from the get-go, but it was possible to get away with it for awhile
>near the end of a century.
>
>            regards, tom lane
>
>
its not my data i am stuck moving this data from a sad access database
designed by a moron. anyway it means i will have to do alot of work on
the script to make it fix that, or just install an old version of pgsql
on a box here and pg_dump the table which seems easier.

> its not my data i am stuck moving this data from a sad access database
> designed by a moron. anyway it means i will have to do alot of work on
> the script to make it fix that, or just install an old version of pgsql
> on a box here and pg_dump the table which seems easier.

I've ported a lot of data from legacy systems with 6 digit dates in
them (mmddyy), not all of which were addressed for Y2K.

For each system I usually wind up writing one or more to_date functions,
so that I have a consistent set of rules being applied.

Depending upon the specific application, those functions will have
different switchover points between 1900 and 2000 as the base century.

Sometimes I have to supply an additional parameter to help decide when
to switch over.

For example, if the data includes other age-based qualifications, such
as whether someone is a child or a senior citizen, that offers another clue
as to whether '02' in a birthdate is 2002 or 1902.  Depending on how
clean the data is in the first place, that might not fix all inconsistencies,
though.

Another reason for having my own date conversion function is consistency
in dealing with bad dates, like 05/32/2004 or 11/31/2004.

In general, I think that date conversion decisions are an application
design issue, not a database system issue, and the fewer such decisions
that are made by the database, the better.
--
Mike Nolan