Understanding DB2 TIMESTAMP_FORMAT Format

Asked 2 years ago, Updated 2 years ago, 136 views

I'd like to do a SELECT in DB2 in the format YYYY-MM-DD HH:MM:SS


on DB Column A: 20200605
Column B: 120000
Since it is defined as NUMBER type as shown in , the two columns were converted to CHAR type once, and after attaching them with CONCAT, TIME_STAMP_FORMAT converted them to date type in the format 'YYYY-MM-DD HH:24:MI:SS'.
Then, for some reason, the result was '2020-06-05-12.00.00.000000'.
I expected it to be 2020-06-05 12:00:00.

Try
SELECT TIMESTAMP_FORMAT(CONCAT(CHAR(20200101), CHAR(00000000))), 'YYYY-MM-DD HH:24:MI:SS')
FROM TEST_TABLE;
When I tested it as shown in , the result was '2020-01-01-00.00.00.000000'.
Is this the specification of TIMESTAMP_FORMAT?
If so, how can I get the results I'm looking for?

sql db2

2022-09-30 10:14

1 Answers

Maybe this is what you need?

VALUES TIMESTAMP (TIMESTAMP_FORMAT (CONCAT(CHAR(20200101), DIGITS(DEC(CHAR(00000000), 6))), 'YYYYYMMDDH24MISS', 0)

returns

2020-01-01 00:00:00

or you could use e.g.

VALUES TIMESTAMP ('2020-01-01'::DATE, 0)

to get the same result


2022-09-30 10:14

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.