By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,809 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,809 IT Pros & Developers. It's quick & easy.

two digit years in inserts

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Justin Wyer <ju****@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #2

P: n/a
Tom Lane wrote:
Justin Wyer <ju****@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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #3

P: n/a
> 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.