I have a column(say date_col) which is integers and contains values which can or cannot be converted into date type. now i have to select the NOT-CONVERTABLE values as "NULL" and CAST the convertable values into DATE(May be Using a case statement). Can some one help me as how to accomplish this??
P.S-> I am using Teradata 14.0
1) I cannot join with SYS_CALENDAR as the values are not of this century.(plus there are many such columns,so join has perfomance issues)
2) I cannot use UDF's(as i am on a client machine i cant install them as well)
3) I have come across Dnoeth's Solution where we reverse engineer the way teradata stores date and put a huge case statement diving it with 100000 and checking the year and then month and so on. but iam looking for a simpler solution.
Solution of following kind is appreciated :
1) An exception where error 2666 : Invalid date supplied is handled and the handler returns NULL for invalid values.
2) A 2-3 line check which can tell me as whether the integer is valid or the cast is valid.
Apologies for long mail,just wanted to be clear.Thanks all in advance
I guess you could filter,
you might want to add a date range to the calender table.
SEL col FROM tbl
WHERE col IN (SEL CAST(Calendar_date AS INTEGER) FROM sys_calendar.calendar)
The above solution may not be possible in my case because the values of intgers that can be converted are not of this century. And the Sys_calendar has dates only of this century.
I'm not sure what you mean,
MAX(CALENDAR_DATE) FROM sys_calendar.calendar;
Calendar is also an issue and you cannot use it ..... hmm.You said you use exception, it seems you are using stored proc. If you loop through, each one and check the validation with multiple CASE when , then it is okay. Data quality-> data governance, data is important.
If you know your data in advance, you can write CASE when and the rest you can direct to a temporary table maybe for analysis.
In other Databases too , it is done that way, since it is row by row processing.