All Forums Database
ravimans 54 posts Joined 02/14
24 May 2015
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.

 

VandeBergB 182 posts Joined 09/06
24 May 2015

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

Some drink from the fountain of knowledge, others just gargle.

ravimans 54 posts Joined 02/14
24 May 2015

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

yuvaevergreen 93 posts Joined 07/09
25 May 2015

if its TD14, try regex
 
 

dnoeth 4628 posts Joined 11/04
25 May 2015

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'
 

Dieter

ravimans 54 posts Joined 02/14
25 May 2015

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.

yuvaevergreen 93 posts Joined 07/09
01 Jun 2015

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.
 
 

gerardo 13 posts Joined 10/09
15 Jun 2015

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 

You must sign in to leave a comment.