Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: timestamp_iso - sql0443n

    $ db2 "values timestamp_iso('15.32.00')"

    1
    --------------------------
    SQL0443N Routine "SYSFUN.TIMESTAMP_ISO" (specific name "TIMESTAMP_ISO4") has
    returned an error SQLSTATE with diagnostic text "SYSFUN:06". SQLSTATE=38552


    06

    Invalid timestamp format


    I tried with v9.7 FP4.


    Does anyone know what's wrong?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Timestamps have a date component. You need to supply the date also.

    Andy

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    The same works with v9.5 (and I'm told it also works with v9.1).

    v9.5:

    $ db2 "values timestamp_iso('15.32.00')"

    1
    --------------------------
    2011-05-04-15.32.00.000000

    1 record(s) selected.




    From the manual (TIMESTAMP_ISO scalar function):

    v9.5:
    The argument must be a date, time, or timestamp, or a valid character string representation of a date, time or timestamp that is neither a CLOB nor a LONG VARCHAR. In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed.


    v9.7:
    The expression must return a value that is a built-in CHAR, VARCHAR, DATE, TIME, or TIMESTAMP data type. In a Unicode database, if a supplied argument has a GRAPHIC or VARGRAPHIC data type, it is first converted to a character string before evaluating the function. A string expression must return a valid character string representation of a date or timestamp.


    Is it broken in v9.1 and v9.5 or v9.7?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    IBM must have changed something between 9.5 and 9.7. If you read the last sentence of the 9.7 definition you gave, a string that is a time is no longer valid.

    Andy

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I agree with Andy.

    Moreover, V9.5 definition wrote clearly:
    The argument must be ....., or a valid character string representation of a date, time or timestamp that is neither a CLOB nor a LONG VARCHAR.

    I thought that v9.7 definition lacks consisitency.
    V9.7 accepts DATE, TIME or TIMESTAMP data type, however it accepts only a character string representation of a DATE or TIMESTAMP.
    V9.5 accepts three data types same as v9.7 and a valid character string representation of them.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Thank you. I found that this change is related to the next paragraph in the v9.7 manual:

    The TIMESTAMP_ISO function is generally defined as deterministic. If the first argument has the TIME data type, then the function is not deterministic because the CURRENT DATE is used for the date portion of the timestamp value.

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I have another question. How can I provide a string representation of a time and return the timestamp similar to the way it worked with timestamp_iso before v9.7 change?


    $ db2 "?????? ('15.32.00')"

    1
    --------------------------
    2011-05-04-15.32.00.000000

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some examples:
    Code:
    ------------------------------ Commands Entered ------------------------------
    -- Tested on DB2 Express-C 9.7.4 on Windows.
    SELECT time_string
         , TIMESTAMP(CURRENT_DATE , time_string) AS to_timestamp_1
         , TO_DATE(time_string , 'hh24.mi.ss')   AS to_timestamp_2
         , TIMESTAMP_ISO( TIME(time_string) )    AS to_timestamp_3
     FROM  (VALUES '15.32.00' , '15:32:00' ) p(time_string)
    ;
    ------------------------------------------------------------------------------
    
    TIME_STRING TO_TIMESTAMP_1             TO_TIMESTAMP_2             TO_TIMESTAMP_3            
    ----------- -------------------------- -------------------------- --------------------------
    15.32.00    2011-05-06-15.32.00.000000 2011-05-01-15.32.00.000000 2011-05-05-15.32.00.000000
    15:32:00    2011-05-06-15.32.00.000000 2011-05-01-15.32.00.000000 2011-05-05-15.32.00.000000
    
      2 record(s) selected.
    Last edited by tonkuma; 05-05-11 at 19:00. Reason: Corrected careless mistake from TIME('15.32.00') to TIME(time_string)

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    ------------------------------ Commands Entered ------------------------------
    -- Tested on DB2 Express-C 9.7.4 on Windows.
    DESCRIBE
    SELECT time_string
         , TIMESTAMP(CURRENT_DATE , time_string) AS to_timestamp_1
         , TO_DATE(time_string , 'hh24.mi.ss')   AS to_timestamp_2
         , TIMESTAMP_ISO( TIME(time_string) )    AS to_timestamp_3
     FROM  (VALUES '15.32.00' , '15:32:00' ) p(time_string)
    ;
    ------------------------------------------------------------------------------
    
     Column Information
    
     Number of columns: 4
    
     SQL type              Type length  Column name                     Name length
     --------------------  -----------  ------------------------------  -----------
     448   VARCHAR                   8  TIME_STRING                              11
     392   TIMESTAMP                26  TO_TIMESTAMP_1                           14
     392   TIMESTAMP                26  TO_TIMESTAMP_2                           14
     393   TIMESTAMP                26  TO_TIMESTAMP_3                           14
    Last edited by tonkuma; 05-05-11 at 19:02. Reason: Corrected careless mistake from TIME('15.32.00') to TIME(time_string)

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    $ db2 "values timestamp_iso('15.32.00')"

    1
    --------------------------
    SQL0443N Routine "SYSFUN.TIMESTAMP_ISO" (specific name "TIMESTAMP_ISO4") has
    returned an error SQLSTATE with diagnostic text "SYSFUN:06". SQLSTATE=38552


    $db2 "values timestamp_iso(time('15.32.00'))"

    1
    --------------------------
    2011-05-05-15.32.00.000000

    1 record(s) selected.



    Thanks, Tonkuma.

Posting Permissions

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