Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

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


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


Subj:   Re: Converting to date format
 
From:   Tewksbury, Kevin

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020