teradata yyyy-m-d格式varchar转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;