Teradata SQL

刚用SQL语句查询的时候发现了一个问题

CREATE SET TABLE PD_AUTO.ETL_Received_File ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ETL_System CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ETL System Name' NOT NULL,
      ETL_Job VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ETL Job Name' NOT NULL,
      JobSessionID INTEGER TITLE 'Job Session ID' NOT NULL,
      ReceivedFile VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Received File Name' NOT NULL,
      FileSize DECIMAL(18,0) TITLE 'File Size',
      ExpectedRecord INTEGER TITLE 'Expected Record',
      ArrivalTime CHAR(19) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Arrival Time',
      ReceivedTime CHAR(19) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Received Time',
      Location VARCHAR(128) CHARACTER SET LATIN NOT CASESPECIFIC,
      Status CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX XAK1ETL_Received_File ( ETL_System ,ETL_Job ,
ReceivedFile );

ArrivalTime 为CHAR(19)类型，在用CAST函数做转换的时候报错

select cast(ArrivalTime as Date FORMAT 'YYYYMMDD')
from pd_auto.etl_received_file

错误信息为Invalid date supplied for ETL_Received_File.ArrivalTime

不知道问题的原因所在，试了好几个写法

select CAST(Trim(substr(ArrivalTime,1,10)))  as Date format 'YYYYMMDD')
from pd_auto.etl_received_file

这样写也不行，FORMAT函数没起到作用得到的结果为2009-11-19，字符串没有被格式化

 

后来换了一种写法

select *
from pd_auto.etl_received_file
where CAST(CAST(ArrivalTime  as timestamp(0)) as Date format 'YYYYMMDD') >= Date '2009-12-01'

这样写没有出现错误

 

 

不知道cast函数为什么就不能直接将char类型转换为date型……

 

内置函数cast ... to date不支持这种格式，没有replace内置函数，因此只能用类似自定义函数的功能。teradata仅支持基于c语言的函数，不支持teradta sql函数，因此必须用sp或在sql中搞定。

 

用字符串列替换'2009-1-3'即可，就是这么个逻辑，不过可以考虑把这些重复的做到一个子查询中。

sel cast(Substr('2009-1-3',1,4) ||
case when char(substr('2009-1-3',Index('2009-1-3','-')+1,
Index(
  Case 
   When Position('-' In '2009-1-3') > 0
Then Substr('2009-1-3',1,Position('-' In '2009-1-3') - 1) || '*' || Substr('2009-1-3',Position('-' In '2009-1-3') + character_length('-'))
   Else '2009-1-3'
  End,'-')-(Index('2009-1-3','-')+1)))=1 then '0' ||  substr('2009-1-3',Index('2009-1-3','-')+1,
Index(
  Case 
   When Position('-' In '2009-1-3') > 0
Then Substr('2009-1-3',1,Position('-' In '2009-1-3') - 1) || '*' || Substr('2009-1-3',Position('-' In '2009-1-3') + character_length('-'))
   Else '2009-1-3'
  End,'-')-(Index('2009-1-3','-')+1))
  else
  substr('2009-1-3',Index('2009-1-3','-')+1,
Index(
  Case 
   When Position('-' In '2009-1-3') > 0
Then Substr('2009-1-3',1,Position('-' In '2009-1-3') - 1) || '*' || Substr('2009-1-3',Position('-' In '2009-1-3') + character_length('-'))
   Else '2009-1-3'
  End,'-')-(Index('2009-1-3','-')+1))
end ||

case when char(substr(Case 
   When Position('-' In '2009-1-3') > 0
Then Substr('2009-1-3',1,Position('-' In '2009-1-3') - 1) || '*' || Substr('2009-1-3',Position('-' In '2009-1-3') + character_length('-'))
   Else '2009-1-3'
  End,Index(Case 
   When Position('-' In '2009-1-3') > 0
Then Substr('2009-1-3',1,Position('-' In '2009-1-3') - 1) || '*' || Substr('2009-1-3',Position('-' In '2009-1-3') + character_length('-'))
   Else '2009-1-3'
  End,'-')+1))=1 then '0' || substr(Case 
   When Position('-' In '2009-1-3') > 0
Then Substr('2009-1-3',1,Position('-' In '2009-1-3') - 1) || '*' || Substr('2009-1-3',Position('-' In '2009-1-3') + character_length('-'))
   Else '2009-1-3'
  End,Index(Case 
   When Position('-' In '2009-1-3') > 0
Then Substr('2009-1-3',1,Position('-' In '2009-1-3') - 1) || '*' || Substr('2009-1-3',Position('-' In '2009-1-3') + character_length('-'))
   Else '2009-1-3'
  End,'-')+1)
else
substr(Case 
   When Position('-' In '2009-1-3') > 0
Then Substr('2009-1-3',1,Position('-' In '2009-1-3') - 1) || '*' || Substr('2009-1-3',Position('-' In '2009-1-3') + character_length('-'))
   Else '2009-1-3'
  End,Index(Case 
   When Position('-' In '2009-1-3') > 0
Then Substr('2009-1-3',1,Position('-' In '2009-1-3') - 1) || '*' || Substr('2009-1-3',Position('-' In '2009-1-3') + character_length('-'))
   Else '2009-1-3'
  End,'-')+1)
end as date format 'yyyymmdd') char_to_date;