Re: two digit years in inserts

From: Mike Nolan <nolan(at)gw(dot)tssi(dot)com>
To: justin(at)isogo(dot)co(dot)za (Justin Wyer)
Cc: tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane), pgsql-general(at)postgresql(dot)org
Subject: Re: two digit years in inserts
Date: 2004-10-06 15:06:00
Message-ID: 200410061506.i96F61V8014139@gw.tssi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

> 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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Mark Gibson 2004-10-06 15:09:22 Re: Commands to browse current connections and processes
Previous Message Bruno Wolff III 2004-10-06 15:00:30 Re: Mailing