Time diff - Epoch time (first shot)

goldfishhh · #1 (**permalink**) 05-28-10, 15:35

I'm trying to get an epoch timestamp created for two date/times and the system keeps saying that I have a missplaced ":". I just can't see where I went wrong.

DT_DT is DATETIME format and TM_TXT is VARCHAR(255)

Code:

        ,TIMESTAMPDIFF(2,(TIMESTAMP(1970-01-01,00:00:02.06)          
          TIMESTAMP(  (SUBSTR(CHAR(DT_DT),1,4))        || '-' ||     
                      (SUBSTR(CHAR(DT_DT),6,2))        || '-' ||     
                      (SUBSTR(CHAR(DT_DT),9,2))        || ',' ||     
            "0"                                               ||     
            CHAR(SUBSTR(TM_TXT,LOCATE(':',TM_TXT)-1,1))|| ":" ||     
            CHAR(SUBSTR(TM_TXT,LOCATE(':',TM_TXT)+1,2))|| ":" ||     
            CHAR(SUBSTR(TM_TXT,LOCATE(':',TM_TXT)+4,2))|| '.7'       
                          )                                          
                       )                                             
                                              -- WHACKYTIME,         

---------+---------+---------+---------+---------+---------+---------+--------
DSNT408I SQLCODE = -104, ERROR:  ILLEGAL SYMBOL ":". SOME SYMBOLS THAT MIGHT  
         BE LEGAL ARE: MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE 
         HOURS                                                                
DSNT418I SQLSTATE   = 42601 SQLSTATE RETURN CODE                              
DSNT415I SQLERRP    = DSNHPARS SQL PROCEDURE DETECTING ERROR                  
DSNT416I SQLERRD    = 3 0  0  -1  1198  502 SQL DIAGNOSTIC INFORMATION        
DSNT416I SQLERRD    = X'00000003'  X'00000000'  X'00000000'  X'FFFFFFFF'      
         X'000004AE'  X'000001F6' SQL DIAGNOSTIC INFORMATION                  
---------+---------+---------+---------+---------+---------+---------+--------