timestamp_iso - sql0443n

db2girl · #1 (**permalink**) 05-04-11, 12:33

$ 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?

ARWinner · #2 (**permalink**) 05-04-11, 12:56

Timestamps have a date component. You need to supply the date also.

Andy

db2girl · #3 (**permalink**) 05-04-11, 13:15

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?

ARWinner · #4 (**permalink**) 05-04-11, 13:24

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

tonkuma · #5 (**permalink**) 05-04-11, 14:30

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.

db2girl · #6 (**permalink**) 05-04-11, 21:01

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.

db2girl · #7 (**permalink**) 05-04-11, 21:45

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

tonkuma · #8 (**permalink**) 05-04-11, 23:18

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.

tonkuma · #9 (**permalink**) 05-04-11, 23:19

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

db2girl · #10 (**permalink**) 05-04-11, 23:49

$ 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.

Internet Services

Web Development

Digital Marketing

Consumer Tech