RE: SQL emergency question
Caveat:
This just occurred to me. 'where coltype = 7' indicates a date field.
You may have to search out datetime fields as well.
-----Original Message-----
From: Scott Black
Sent: Tuesday, June 22, 1999 12:35 PM
To: '-=Eclypse=-'
Cc: 'Informix-List (E-mail)
Subject: RE: SQL emergency question
I had a similar need some time back and used the following:
unload to "bad_dates.sql"
select "update " || tabname || "set ", colname || "='06/21/1999' where " || colname || " = '07/14/2019';"
from systables, syscolumns
where systables.tabid = syscolumns.tabid
and systables.tabid > 99
and coltype = 7
order by 1
I haven't tested this, but you get the idea. Once you run this
on every database, you will have a script that you can run to alter all
dates.
HTH
-----Original Message-----
From: -=Eclypse=- [SMTP:eclypse@cdc.net]
Posted At: Tuesday, June 22, 1999 11:50 AM
Posted To: Informix
Conversation: SQL emergency question
Subject: SQL emergency question
This past weekend we upgraded one of our RS/6000s and in
the process the NVRAM on
the box got corrupted. The date got changed to 7/14/2019
and went unnoticed until
Monday morning. We run BaaN and Informix on the backend.
Several transactions
were processed before we caught the date error. We have
approximately 80000 tables
in our database and several of those have columns of
type "DATE".
What I need help with is the wording on an SQL statement
to find any table with a
column type of "DATE" with the value in that column
equal to "07/14/2019" and
change it to "06/21/1999" anywhere in the database. Any
help is appreciated!
--
Jon Freeland<*>
Informix DBA
Miller Industries, Inc.
jon@millerind.com
eclypse@babylon5.cdc.net