I have learned that subtracting two dates in Db2 results in an integer which is to be interpreted as an interval:
SELECT current_date - '1943-02-25' FROM SYSIBM.DUAL;
-- 820020: 82 years, 0 months, 20 days
In this case, I can use substring() and right() to pull it apart:
WITH cte(age) AS
(
SELECT current_date - '1943-02-25' FROM SYSIBM.DUAL
)
SELECT left(age,2) AS yyyy, substr(age,3,2) AS mm, right(age,2) AS dd
FROM cte;
The only problem is if the age is more than 99 years, then it’s the first three or four digits for the years, and the months component starts further down.
If I were to use a regular expression, I would have something like this:
^(\d{2,4})(\d{2})(\d{2})$
I know that Db2 has a regexp_substr() aka regexp_extract() function but I can’t see how the above expression can be used. Everything I’ve read uses a simple expression like o. and nothing with a variable number of characters.
What is the way to extract a variable number of characters for the year and month? Do I use regexp_substr() or something else?
I’m running Db2 12 in a Docker Linux container.
Is there any reason why you can't use the EXTRACT() function here:
WITH cte(age) AS (
SELECT current_date - '1943-02-25'
FROM SYSIBM.DUAL
)
SELECT
EXTRACT(YEAR FROM age) AS yyyy,
EXTRACT(MONTH FROM age) AS mm,
EXTRACT(DAY FROM age) AS dd
FROM cte;