因此，我在db2中有一个表示日期字段的整数列。这不一致数据子集的格式化，即2017-2019
| MDATE |
|---|
| 20070730 |
| 1012017 |
| 31122019 |
WITH MYTAB (MDATE) AS
(
VALUES
20070730
, 1012017
, 31122019
)
SELECT
MDATE
, CASE
WHEN MDATE BETWEEN 1012017 and 31122019 and MDATE NOT BETWEEN 20070101 AND 20221031
THEN
--TIMESTAMP_FORMAT(CHAR(MDATE),'DDMMYYYY')
TIMESTAMP_FORMAT
(
CASE LENGTH (TO_CHAR (MDATE))
WHEN 7 THEN SUBSTR (TO_CHAR (MDATE), 1, 2) || '0' || SUBSTR (TO_CHAR (MDATE), 3)
ELSE TO_CHAR (MDATE)
END
, 'DDMMYYYY'
)
ELSE TIMESTAMP_FORMAT(CHAR(MDATE),'YYYYMMDD')
END AS TIMESTAMP
FROM MYTAB
| MDATE | TIMESTAMP |
|---|---|
| 20070730 | 2007-07-30-00.00.00.000000 |
| 1012017 | 2017-01-10-00.00.00000000 |
| 31122019 | 2019-12-31-00.00.00000000 |