Archives of the TeradataForum

Message Posted: Fri, 14 Dec 2012 @ 19:30:59 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I could not find any Teradata conversion for the AM/PM so I constructed a messy case statement, but it does work. The only caveat is that when the hour becomes two digits then there must only be one space between the year and the hour, otherwise this will not work and you would need to manipulate further. I am sure that there are probably other DBA's out there that may have a better solution. If nothing else it was a fun exercise.

     select case when substr('Aug 27 2009  7:07:43:470PM',25,2) = 'AM'
                        then case when substr('Aug 27 2009
     7:07:43:470PM',13,2) = '12'
                                      then cast(cast(cast(substr('Aug 27 2009
     7:07:43:470PM',1,11) as date FORMAT 'MMMBDDBYYYY') as date FORMAT
     'YYYY-MM-DD')||' '||cast(cast('00'||cast(substr('Aug 27 2009
     7:07:43:470PM',15,6) as CHAR(6))||'.'||cast(substr('Aug 27 2009
     7:07:43:470PM',22,3) as CHAR(7)) as time(6)) as char(15)) as char(26))
                                      else cast(cast(cast(substr('Aug 27 2009
     7:07:43:470PM',1,11) as date FORMAT 'MMMBDDBYYYY') as date FORMAT
     'YYYY-MM-DD')||' '||cast(cast(cast(zeroifnull(substr('Aug 27 2009
     7:07:43:470PM',13,1)) as char(1))||cast(substr('Aug 27 2009
     7:07:43:470PM',14,7) as CHAR(7))||'.'||cast(substr('Aug 27 2009
     7:07:43:470PM',22,3) as CHAR(7)) as time(6)) as char(15)) as char(26))
                                      end
                             else cast(cast(cast(substr('Aug 27 2009
     12:07:43:470AM',1,11) as date FORMAT 'MMMBDDBYYYY') as date FORMAT
     'YYYY-MM-DD')||' '||cast(cast(cast(zeroifnull(substr('Aug 27 2009
     7:07:43:470PM',13,1)) as char(1))||cast(substr('Aug 27 2009
     7:07:43:470PM',14,7) as CHAR(7))||'.'||cast(substr('Aug 27 2009
     7:07:43:470PM',22,3) as CHAR(7)) as time(6)) + interval '12' hour as
     char(15)) as char(26))
                end

KRT

	<Prev	Next>		<<First	<Prev	Next>	Last>>