Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: timestamp format incorrect?

    Hi,

    Can you please tell what I'm doing wrong here? Thanks!!

    db2 "select TIMESTAMP(CHAR(DATE(CURRENT TIMESTAMP), ISO), TIME(CHAR(HOUR(CURRENT TIMESTAMP)) || '.' || CHAR('00') || '.' || CHAR('00'))) from sysibm.sysdummy1"

    1
    --------------------------
    SQL0180N The syntax of the string representation of a datetime value is
    incorrect. SQLSTATE=22007



    This looks good --

    db2 "select TIMESTAMP(CHAR(DATE(CURRENT TIMESTAMP), ISO), TIME(CHAR('00') || '.' || CHAR('00') || '.' || CHAR('00'))) from sysibm.sysdummy1"

    1
    --------------------------
    2010-10-26-00.00.00.000000

    1 record(s) selected.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CHAR(HOUR(CURRENT TIMESTAMP)) includes trailing blanks, like this:

    Code:
    ------------------------------ Commands Entered ------------------------------
    select CHAR(HOUR(CURRENT TIMESTAMP)) || '.' || CHAR('00') || '.' || CHAR('00') from sysibm.sysdummy1;
    ------------------------------------------------------------------------------
    
    1                
    -----------------
    3          .00.00
    
      1 record(s) selected.
    Use VARCHAR rather than CHAR, like...

    Code:
    ------------------------------ Commands Entered ------------------------------
    select TIMESTAMP(CHAR(DATE(CURRENT TIMESTAMP), ISO), TIME(VARCHAR(HOUR(CURRENT TIMESTAMP)) || '.' || CHAR('00') || '.' || CHAR('00'))) from sysibm.sysdummy1;
    ------------------------------------------------------------------------------
    
    1                         
    --------------------------
    2010-10-27-03.00.00.000000
    
      1 record(s) selected.

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    thanks, I get this now --

    db2 "select TIMESTAMP(CHAR(DATE(CURRENT TIMESTAMP), ISO), TIME(VARCHAR(HOUR(CURRENT TIMESTAMP)) || '.' || CHAR('00') || '.' || CHAR('00'))) from sysibm.sysdummy1"
    SQL0440N No authorized routine named "VARCHAR" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884



    btw, am using version 8.2 Db2 UDB

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    btw, am using version 8.2 Db2 UDB
    So, try RTRIM(CHAR(HOUR(CURRENT TIMESTAMP))), like ...

    Code:
    ------------------------------ Commands Entered ------------------------------
    select TIMESTAMP(CHAR(DATE(CURRENT TIMESTAMP), ISO), TIME(RTRIM(CHAR(HOUR(CURRENT TIMESTAMP))) || '.' || CHAR('00') || '.' || CHAR('00'))) from sysibm.sysdummy1;
    ------------------------------------------------------------------------------
    
    1                         
    --------------------------
    2010-10-27-04.00.00.000000
    
      1 record(s) selected.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want to get truncated timestamp, here is an example.

    Code:
    ------------------------------ Commands Entered ------------------------------
    select TIMESTAMP( SUBSTR(CHAR(CURRENT TIMESTAMP), 1, 14) || '00.00' ) from sysibm.sysdummy1;
    ------------------------------------------------------------------------------
    
    1                         
    --------------------------
    2010-10-27-04.00.00.000000
    
      1 record(s) selected.

  6. #6
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by tonkuma View Post
    If you want to get truncated timestamp, here is an example.

    Code:
    ------------------------------ Commands Entered ------------------------------
    select TIMESTAMP( SUBSTR(CHAR(CURRENT TIMESTAMP), 1, 14) || '00.00' ) from sysibm.sysdummy1;
    ------------------------------------------------------------------------------
    
    1                         
    --------------------------
    2010-10-27-04.00.00.000000
    
      1 record(s) selected.


    great! thanks..this is perfect!

Posting Permissions

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