Bad data in table , need suggestion to isolate the problem

Database
Enthusiast

Bad data in table , need suggestion to isolate the problem

Hello,

I have a unique situation here and I need your suggestions help to achieve it.

I have close to 10000 tables where I have a timestamp fields which (probably) has bad data(long story behind this).

Problem is : If I run select on any of the table which has bad timestamp value it fails with a  error.

I want a suggestion , how to find out all the tables which has issues , I cna not go through each and every table and run a select. 

4 REPLIES
Junior Contributor

Re: Bad data in table , need suggestion to isolate the problem

You should contact Teradata support immediately and tell them the long story :-)

Enthusiast

Re: Bad data in table , need suggestion to isolate the problem

Timestamp columns have invalid timestamp values?...

Teradata Employee

Re: Bad data in table , need suggestion to isolate the problem

Like the previous post, I wonder what data type the timestamps are stored in. 

if there is any kind of naming convention(s) that allows selection of the column names which contain the timestamps, then a query can be written against the dictionary views such that the query result is a set of SQL select statements against those columns. That result can be stored and then executed to accomplish the requirement. If no naming convention, then a list will have to be made of table and column names, then the SQL statements can be generated from that list.

Teradata Employee

Re: Bad data in table , need suggestion to isolate the problem

It's possible that data can be stored as a valid TIMESTAMP value but it's so close to the min/max allowable that applying the local / session time zone offset causes the result to be out of range. (For example, you can't display the UNTIL_CHANGED timestamp value AT LOCAL when the session time zone is ahead of UTC.)

Depending on how you got to that point, ALTER TABLE FROM TIME ZONE might be used if all rows need time zone shift, or you can set session time zone offset to 00:00 to run queries to find (and potentially to fix) just the offending rows. 

But you still have to check each table individually, there's no way around that.