Validate integers that can be converted into date

Database
Enthusiast

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

cheers,

Subbu

Tags (1)
6 REPLIES
Enthusiast

Re: Validate integers that can be converted into date

Enthusiast

Re: Validate integers that can be converted into date

Hi Glass,

thanks for you reply

and i have tried this post by Dnoeth and it works in my case

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

  THEN 1 ELSE 0

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????

 

Enthusiast

Re: Validate integers that can be converted into date

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)

Rglass

Enthusiast

Re: Validate integers that can be converted into date

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.

Cheers,

Subbu

Enthusiast

Re: Validate integers that can be converted into date

I'm not sure what you mean,

SEL MIN(CALENDAR_DATE),

MAX(CALENDAR_DATE) FROM sys_calendar.calendar;

1900-01-01 2100-12-31

Enthusiast

Re: Validate integers that can be converted into date

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.