finding a row with an invalid date

[jones1]

Hi, I have a table with data in a character field, that I think should be able to be stored in a date field. So, I created a copy of the table, changed the column from character to date and am attempting to cast and insert all rows from the one table into the other table. However, I keep getting a teradata invalid date message. How can I find the problem rows with an invalid date?

 

[BillDHS]

Try grouping, counting, and sorting.

If you group and count, you should see invalid codes with low counts (hopefully). Also sort and look for invalid dates like Feb 30 or Apr 31. One other thing make sure the "blank" dates are NULL, not spaces, spaces are invalid .

 

[tdatgod]

Is there some way you can CAST syscalendar.calendar fields to create all the valid dates as a CHAR string to match your field definition and then do something like....

sel * from your_table
where your_date not in
( sel
trim(day_of_month )
|| '/' ||
trim(month_of_year)
|| '/' ||
trim(year_of_calendar) as valid_dates
from sys_calendar.calendar)
;