Teradata valid date check without udf

Database
Enthusiast

Teradata valid date check without udf

Hi All,

I am having a column with data type as CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL but there are chances the same column will hold date value as well. We need to check if the column has valid date or not? If valid date then we need to store the same or else we need to default it. I want to try this without using udf but the other method i tried is to cast to check for yyyy or mm or dd as below:

sel

CAST(SUBSTR(cast(col1 as char(25)),1,4) as integer) as dt

from tbname

I am getting error as: SELECT Failed 2621: Bad character in format or data.

Is there any other way we can check for valid date in TD? I am using TD 14.

7 REPLIES
Enthusiast

Re: Teradata valid date check without udf

Quick thought, you  could always do a left outer join to the sys_calendar.calendar view and update the data to the default where col1 is null...

Enthusiast

Re: Teradata valid date check without udf

I don't want to join with sys_calendar. Is there any other way?

Enthusiast

Re: Teradata valid date check without udf

if its TD14, try regex

Senior Apprentice

Re: Teradata valid date check without udf

How does the date look like, is there a fixed format?

Leading zeroes for month and day?

Any other characters before/after the date?

e.g. '2015-05-15'

Enthusiast

Re: Teradata valid date check without udf

Hi Dieter,

There is no fixed format. Currently that column has 0, 50, AC etc...as values. Might be in future we will get date values. I am assuming that should be in yyyy-mm-dd format.

Enthusiast

Re: Teradata valid date check without udf

IF its yyyy-mm-dd, regexp_similar can be used in td14

REGEXP_SIMILAR('-aa1sa22212-12-12asas','([a-zA-Z\-0-9@#$%!^&*()\+\,\.]{0,}[0-9]{4}-[0-9]{2}-[0-9]{2}[a-zA-Z\-0-9@#$%!^&*()\+\,\.]{0,})','i')

This gives 1, if true.

Teradata Employee

Re: Teradata valid date check without udf

Just with case  (returns valid date or null in case invalid date)

(CASE WHEN SUBSTR(DATE_COLUMN,9,2) BETWEEN '01' AND '31'

         AND SUBSTR(DATE_COLUMN,6,2) IN ('01','03','05','07','08','10','12')

         AND SUBSTR(DATE_COLUMN,1,1) IN ('1','2')

         AND SUBSTR(DATE_COLUMN,2,1) IN ('8','9','0')

         AND SUBSTR(DATE_COLUMN,3,1) BETWEEN '0' AND '9'

         AND SUBSTR(DATE_COLUMN,4,1) BETWEEN '0' AND '9'

     THEN DATE_COLUMN  

     WHEN SUBSTR(DATE_COLUMN,9,2) BETWEEN '01' AND '30'

         AND SUBSTR(DATE_COLUMN,6,2) IN ('04','06','09','11')

         AND SUBSTR(DATE_COLUMN,1,1) IN ('1','2')

         AND SUBSTR(DATE_COLUMN,2,1) IN ('8','9','0')

         AND SUBSTR(DATE_COLUMN,3,1) BETWEEN '0' AND '9'

         AND SUBSTR(DATE_COLUMN,4,1) BETWEEN '0' AND '9'

     THEN DATE_COLUMN   

     WHEN SUBSTR(DATE_COLUMN,9,2) BETWEEN '01' AND '28'

         AND SUBSTR(DATE_COLUMN,6,2) IN ('02')

         AND SUBSTR(DATE_COLUMN,1,1) IN ('1','2')

         AND SUBSTR(DATE_COLUMN,2,1) IN ('8','9','0')

         AND SUBSTR(DATE_COLUMN,3,1) BETWEEN '0' AND '9'

         AND SUBSTR(DATE_COLUMN,4,1) BETWEEN '0' AND '9'

     THEN DATE_COLUMN 

     WHEN SUBSTR(DATE_COLUMN,9,2) ='29'

         AND SUBSTR(DATE_COLUMN,6,2) IN ('02')

         AND SUBSTR(DATE_COLUMN,1,1) IN ('1','2')

         AND SUBSTR(DATE_COLUMN,2,1) IN ('8','9','0')

         AND SUBSTR(DATE_COLUMN,3,1) BETWEEN '0' AND '9'

         AND SUBSTR(DATE_COLUMN,4,1) BETWEEN '0' AND '9'

     THEN (CASE WHEN (SUBSTR(DATE_COLUMN,1,4) (INTEGER)) MOD 4 = 0

                and  not ((SUBSTR(DATE_COLUMN,1,4) (INTEGER)) MOD 100 = 0

                          and 

                          (SUBSTR(DATE_COLUMN,1,4) (INTEGER)) MOD 400 <> 0)

                THEN DATE_COLUMN

                ELSE NULL END)

     ELSE NULL END ) (DATE, FORMAT 'YYYY-MM-DD') AS DATE_COLUMN_VALID