Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    54

    Unanswered: Time diff - Epoch time (first shot)

    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                  
    ---------+---------+---------+---------+---------+---------+---------+--------

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ":"s and "0"

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    @goldfishhh: Still haven't figured out string literals, have you?

  4. #4
    Join Date
    Nov 2004
    Posts
    54


    Yea.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, firstly, forget double quotes. Then read the manual for the TIMESTAMP function to find out what are the data types of its arguments.

  6. #6
    Join Date
    Jul 2009
    Posts
    150
    TIMESTAMP('1970-01-01-00:00:02.060000(')

    Also TIMESTAMPDIFF has format TIMESTAMPDIFF(NNN, Char(m))

    Kara

  7. #7
    Join Date
    Nov 2004
    Posts
    54
    Ahhh, there it goes. I got lost in the sea of quotes, commas and most importantly, the syntax.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •