TERADATA中数据类型CHAR转DATE

1）长度为8位,即数据格式为20090101：
CASE
WHEN VALUE_DATE IS NULL OR VALUE_DATE=''
THEN CAST('${NULLDATE}' AS DATE FORMAT 'YYYYMMDD')
WHEN CHARACTERS(TRIM(VALUE_DATE))<>8
OR SUBSTR(TRIM(VALUE_DATE),1,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),1,1) > '9'
OR SUBSTR(TRIM(VALUE_DATE),2,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),2,1) > '9'
OR SUBSTR(TRIM(VALUE_DATE),3,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),3,1) > '9'
OR SUBSTR(TRIM(VALUE_DATE),4,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),4,1) > '9'
OR SUBSTR(TRIM(VALUE_DATE),5,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),5,1) > '1'
OR SUBSTR(TRIM(VALUE_DATE),6,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),6,1) > '9'
OR SUBSTR(TRIM(VALUE_DATE),7,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),7,1) > '3'
OR SUBSTR(TRIM(VALUE_DATE),8,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),8,1) > '9'
OR SUBSTR(TRIM(VALUE_DATE),1,4) = '0000' OR SUBSTR(TRIM(VALUE_DATE),5,2) = '00'
OR SUBSTR(TRIM(VALUE_DATE),7,2) = '00'
OR SUBSTR(TRIM(VALUE_DATE),1,1) = '0'
THEN CAST('${ILLDATE}' AS DATE FORMAT 'YYYYMMDD')
ELSE (
CASE
WHEN ( CAST(SUBSTR(TRIM(VALUE_DATE),7,2) AS INTEGER) < 29 AND SUBSTR(TRIM(VALUE_DATE),5,2) = '02')
OR ( CAST(SUBSTR(TRIM(VALUE_DATE),7,2) AS INTEGER) < 31 AND SUBSTR(TRIM(VALUE_DATE),5,2) <>'02'
AND SUBSTR(TRIM(VALUE_DATE),5,2)<=12)
OR ( CAST(SUBSTR(TRIM(VALUE_DATE),7,2) AS INTEGER) = 31
AND SUBSTR(TRIM(VALUE_DATE),5,2) IN ('01','03','05','07','08','10','12') )
THEN CAST(TRIM(VALUE_DATE) AS DATE FORMAT 'YYYYMMDD')
WHEN SUBSTR(TRIM(VALUE_DATE),5,4) = '0229'
AND ( CAST(SUBSTR(TRIM(VALUE_DATE),1,4) AS INTEGER) MOD 400 = 0 OR
( CAST(SUBSTR(TRIM(VALUE_DATE),1,4) AS INTEGER) MOD 4 = 0
AND CAST(SUBSTR(TRIM(VALUE_DATE),1,4) AS INTEGER) MOD 100 <> 0) )
THEN CAST(TRIM(VALUE_DATE) AS DATE FORMAT 'YYYYMMDD')
ELSE CAST('${ILLDATE}' AS DATE FORMAT 'YYYYMMDD')
END
)
END

2)长度为10位，即数据格式为2009-01-01：
CASE
WHEN VALUE_DATE IS NULL OR VALUE_DATE=''
THEN CAST('${NULLDATE}' AS DATE FORMAT 'YYYYMMDD')
WHEN CHARACTERS(TRIM(VALUE_DATE))<>10
OR SUBSTR(TRIM(VALUE_DATE),1,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),1,1) > '9'
OR SUBSTR(TRIM(VALUE_DATE),2,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),2,1)

> '9'
OR SUBSTR(TRIM(VALUE_DATE),3,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),3,1) > '9'
OR SUBSTR(TRIM(VALUE_DATE),4,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),4,1) > '9'
OR SUBSTR(TRIM(VALUE_DATE),6,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),6,1) > '1'
OR SUBSTR(TRIM(VALUE_DATE),7,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),7,1) > '9'
OR SUBSTR(TRIM(VALUE_DATE),9,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),9,1) > '3'
OR SUBSTR(TRIM(VALUE_DATE),10,1) < '0' OR SUBSTR(TRIM(VALUE_DATE),10,1) > '9'
OR SUBSTR(TRIM(VALUE_DATE),1,4) = '0000' OR SUBSTR(TRIM(VALUE_DATE),6,2) = '00'
OR SUBSTR(TRIM(VALUE_DATE),9,2) = '00'
OR SUBSTR(TRIM(VALUE_DATE),1,1) = '0'
THEN CAST('${ILLDATE}' AS DATE FORMAT 'YYYYMMDD')
ELSE (
CASE
WHEN ( CAST(SUBSTR(TRIM(VALUE_DATE),9,2) AS INTEGER) < 29 AND SUBSTR(TRIM(VALUE_DATE),6,2) = '02')
OR ( CAST(SUBSTR(TRIM(VALUE_DATE),9,2) AS INTEGER) < 31 AND SUBSTR(TRIM(VALUE_DATE),6,2) <>'02'
AND SUBSTR(TRIM(VALUE_DATE),6,2)<=12)
OR ( CAST(SUBSTR(TRIM(VALUE_DATE),9,2) AS INTEGER) = 31
AND SUBSTR(TRIM(VALUE_DATE),6,2) IN ('01','03','05','07','08','10','12') )
THEN CAST(TRIM(VALUE_DATE) AS DATE FORMAT 'YYYY-MM-DD')
WHEN SUBSTR(TRIM(VALUE_DATE),6,2)||SUBSTR(TRIM(VALUE_DATE),9,2) = '0229'
AND ( CAST(SUBSTR(TRIM(VALUE_DATE),1,4) AS INTEGER) MOD 400 = 0 OR
( CAST(SUBSTR(TRIM(VALUE_DATE),1,4) AS INTEGER) MOD 4 = 0
AND CAST(SUBSTR(TRIM(VALUE_DATE),1,4) AS INTEGER) MOD 100 <> 0) )
THEN CAST(TRIM(VALUE_DATE) AS DATE FORMAT 'YYYY-MM-DD')
ELSE CAST('${ILLDATE}' AS DATE FORMAT 'YYYYMMDD')
END
)
END
3)长度不定，里面有这样的格式：2009-1-1，2009-12-31，2009-1-12等：
CASE
WHEN CHAR(TRIM(DUTY_END_DT))=10 THEN CAST(TRIM(DUTY_END_DT) AS CHAR(10))
WHEN CHAR(TRIM(DUTY_END_DT))=8 THEN SUBSTR(TRIM(DUTY_END_DT),1,5)||'0'||SUBSTR(TRIM(DUTY_END_DT),6,2)||'0'||SUBSTR(TRIM(DUTY_END_DT),8,1)
WHEN CHAR(TRIM(DUTY_END_DT))=9 THEN (CASE WHEN SUBSTR(TRIM(DUTY_END_DT),7,1)='-' THEN SUBSTR(TRIM(DUTY_END_DT),1,5)||'0'||SUBSTR(TRIM(DUTY_END_DT),6,4)
ELSE SUBSTR(TRIM(DUTY_END_DT),1,8)||'0'||SUBSTR(TRIM(DUTY_END_DT),9,1)
END)
ELSE '9999-12-31'
END

4)数据为英文日期，格式为 20-DEC-07：
COALESCE('20'||SUBSTR(TRIM(ADD_DATE),8,2)||
(CASE
WHEN SUBSTR(TRIM(ADD_DATE),4,3) ='JAY' THEN '01'
WHEN SUBSTR(TRIM(ADD_DATE),4,3) ='FEB' THEN '02'
WHEN SUBSTR(TRIM(ADD_DATE),4,3) ='MAR'

THEN '03'
WHEN SUBSTR(TRIM(ADD_DATE),4,3) ='APR' THEN '04'
WHEN SUBSTR(TRIM(ADD_DATE),4,3) ='MAY' THEN '05'
WHEN SUBSTR(TRIM(ADD_DATE),4,3) ='JUN' THEN '06'
WHEN SUBSTR(TRIM(ADD_DATE),4,3) ='JUL' THEN '07'
WHEN SUBSTR(TRIM(ADD_DATE),4,3) ='AUG' THEN '08'
WHEN SUBSTR(TRIM(ADD_DATE),4,3) ='SEP' THEN '09'
WHEN SUBSTR(TRIM(ADD_DATE),4,3) ='OCT' THEN '10'
WHEN SUBSTR(TRIM(ADD_DATE),4,3) ='NOV' THEN '11'
WHEN SUBSTR(TRIM(ADD_DATE),4,3) ='DEC' THEN '12'
END) ||SUBSTR(TRIM(ADD_DATE),1,2),’${NULLDATE}’)