Converting a CYYMMDD date in SQL

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

Example: the filter that switches the index off

This is the part that costs real time, and it does not show until the file grows.

Filtering on the converted date feels natural and forces the database to convert every row before it can discard it. The index on DTACYY, if there is one, goes unused:

-- slow: the conversion sits on the left of the comparison
WHERE DATE(TIMESTAMP_FORMAT(DIGITS(DEC(DTACYY + 19000000, 8, 0)), 'YYYYMMDD'))
      BETWEEN '2026-01-01' AND '2026-12-31'

The same selection, comparing on the raw number, leaves the column untouched and the index usable:

-- fast: the column stays as it is, the bounds are already in CYYMMDD
WHERE DTACYY BETWEEN 1260101 AND 1261231

The conversion is for displaying the date, not for searching it. The rule goes beyond this case: any function applied to a column in the WHERE (not just this one) prevents the use of an index on that column.

Releases. TIMESTAMP_FORMAT, DIGITS and DEC are standard SQL functions, present on every supported release. This recipe has no prerequisites.

Sull'esempio del filtro: che una funzione applicata alla colonna nel WHERE impedisca l'uso dell'indice è comportamento noto degli ottimizzatori SQL e vale su Db2 for i, ma non è stato misurato qui. Se entra una misura, va in questa nota.