Re: SQL emergency question
Scott Black wrote:
>
> 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.
... and don't forget columns defined with NOT NULL! The coltype has 256
added when is does not allow nulls.
Then just to be flash, how about something like:
OUTPUT TO PIPE "dbaccess my_database" WITHOUT HEADINGS
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 IN (7, 263)
to do it in one shot.
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
|Mark D. Stock - Informix SA http://www.informix.com |//////// /|
|mailto:mdstock@informix.com http://www.informix.com/idn |///// / //|
|http://www.iiug.org +-----------------------------------+//// / ///|
| Tel: +27 11 807 0313 |What year 2000 bug? year 2000 bug? |/// / ////|
| Fax: +27 838250 2325 |year 2000 bug? year 2000 bug? year |// / /////|
|Cell: +27 83 250 2325 |2000 bug? year 2000 bug? year 1900 |/ ////////|
+----------------------+-----------------------------------+-----------+