All Forums Database
subbu.balu93 13 posts Joined 09/14
15 Sep 2014
Validate integers that can be converted into date

Hi all,
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

Glass 225 posts Joined 04/10
subbu.balu93 13 posts Joined 09/14
15 Sep 2014

Hi Glass,
thanks for you reply
and i have tried this post by Dnoeth and it works in my case


  WHEN   (d / 10000 BETWEEN 1900 AND 9999)  -- correct year, adjust to your minimum date

    AND  (d / 100 MOD 100 BETWEEN 1 AND 12) -- correct month

    AND ((d / 100 MOD 100 IN (1,3,5,7,8,10,12) AND d MOD 100 BETWEEN 1 AND 31)

      OR (d / 100 MOD 100 IN (4,6,9,11) AND d MOD 100 BETWEEN 1 AND 30)

      OR (d / 100 MOD 100 = 2 AND d MOD 100 BETWEEN 1 AND 28

          + CASE WHEN (((d / 10000) MOD 4 = 0) AND ((d / 10000) MOD 100 <> 0))

                   OR  ((d / 10000) MOD 400 = 0) THEN 1 ELSE 0 -- leap year?

            END)) -- day between 1 and 28/29/30/31


END AS ValidDate


But the Problem i have is I have many such columns which i have to check. Isnt there a simple way
where I can cast into date and the values which raise errors can be handled using an exception or something????

Glass 225 posts Joined 04/10
15 Sep 2014

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)

subbu.balu93 13 posts Joined 09/14
15 Sep 2014

Hi Glass,
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.

Glass 225 posts Joined 04/10
15 Sep 2014

I'm not sure what you mean,
MAX(CALENDAR_DATE) FROM sys_calendar.calendar;
1900-01-01 2100-12-31

Raja_KT 1246 posts Joined 07/09
15 Sep 2014

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.

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.