Why does DB2 CHAR() function return '24:00:00' for a time field that is 00:00:00?

Question

I'm querying DB2 on i (AS/400). A plain SELECT statement on a column with datatype of TIME returns 00:00:00, but when I use the CHAR() function it returns '24:00:00'.

I understand that 24:00:00 is a valid time, but why would CHAR() return 24 when the native TIME returs 00?

Answer 1

On V5R4:

CREATE TABLE TEST (FIELD1 TIME NOT NULL WITH DEFAULT)

INSERT INTO TEST VALUES('00:00:00')

SELECT FIELD1, CHAR(FIELD1) FROM TEST

Shows:

FIELD1    CHAR ( FIELD1 ) 
00:00:00     00:00:00

Not sure why you are getting 24:00:00.

I also tried:

INSERT INTO TEST VALUES('24:00:00')

thinking maybe a 24:00:00 value was displaying as '00:00:00' but this shows:

FIELD1    CHAR ( FIELD1 ) 
00:00:00     00:00:00     
24:00:00     24:00:00

for pretty much all Time formats (*HMS, ISO).