Seven-digit numeric date fields — 1260727 for 27 July 2026 — convert without taking
anything apart, using the fact that the "century" digit is 0 for the 1900s and 1 from 2000.
SELECT DATE(TIMESTAMP_FORMAT(DIGITS(DEC(DTACYY + 19000000, 8, 0)), 'YYYYMMDD'))
FROM PRODLIB/ORDERS
Adding 19000000 turns the number into the date in YYYYMMDD form: 1260727 becomes
20260727, and 0991231 becomes 19991231.
To guard against zero dates, which are the norm in these fields when no value has been set yet:
SELECT CASE WHEN DTACYY = 0 THEN NULL
ELSE DATE(TIMESTAMP_FORMAT(DIGITS(DEC(DTACYY + 19000000, 8, 0)), 'YYYYMMDD'))
END AS ORDER_DATE
FROM PRODLIB/ORDERS
Warning
without the zero check the conversion fails and the query stops on the whole result, not on the individual row. That is why a query that worked stops working the day the first record without a date is entered.
Note
the same expression does not apply to six-digit YYMMDD fields, which carry no
century: there, which century is meant is an application decision, not a database one.
Releases. TIMESTAMP_FORMAT, DIGITS and DEC are standard SQL functions, present on
every supported release. This recipe has no prerequisites.
Comments
No comments yet. Be the first to comment!
You need an account to comment. Log in · Sign up