Archives of the TeradataForum

Message Posted: Wed, 07 Dec 2005 @ 14:37:27 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Cast problem : invalid date format

From:		Victor Sokovin

Could any one tell me how to eliminate garbage date values and still do a successful cast operation

for example i want to cast(birth_date as date format 'DDMMYYYY') BUT this column contain some values like '00000000' that prompts me when casting column as : invalid date

Now how to resolve this issue

Data quality is a *big* bag of issues. Individual SQL statements are not supposed to be able to handle them, they are just meant to fail the first time they observe that something is wrong with the data. They are not even supposed to describe exactly what is wrong.

Now, back to this particular case. You could use the so called system calendar, the in-built Teradata table containing basic data on all valid dates from January 1, 1900. Run a query against this calendar and your table and mark, in one way or another, all the rows in your table where the date is not valid. Just as an example, set all invalid birth dates to '00000000'. After that you could refine your original SQL statement in such a way that it only CASTs valid dates (WHERE birth_date <> '00000000').

This is just a rough idea of what could be done. Of course, it is best to put all correcting measures of this kind in the ETL processes, before the data reaches the users. ETL processes will usually not be able to look up valid birth dates for your customers but they could (and should) at least make the data more usable.

Regards,

Victor


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference