Subtracting days from a "date" when it is a number in a file

The germ for this post came from a question:

How can I subtract 35 days from a date in my files using SQL? The date is a packed number

Most of us who deal with older databases find that the "dates" in the files are not true dates fields, they are numbers masquerading as dates. I have, in a previous post, shown how to change a numeric representation of a date to a date with SQL, this is taking it a step further by converting the date back to a number and updating the file with the new value.

In these examples I will be using the numeric representation of the date in two formats:

1. YYYYMMDD
2. CYYMMDD

I am not going to test 6 long "dates" as I hope that after Y2K they are no longer found in IBM i databases.

I decided to do this in two steps: Firstly I would test my SQL logic by creating a SQL statement where each step of the conversion would be a column in the results. Then once I have confirmed my logic is valid I would then update the file.

Before I start showing SQL I need to show you the DDS file I will be using for my testing.

A R TESTFILER A YYMD 8P 0 A CYMD 7P 0

I think the names of the fields and their lengths adequately which field is for what date format.

I added three records to the file:

YYMD CYMD ---------- --------- 20,200,518 1,200,518 20,200,312 1,200,312 20,190,222 1,190,222

 

YYYYMMDD field

Here is the SQL statement I built to test my logic.

01 SELECT YYMD, 02 TIMESTAMP_FORMAT(CHAR(YYMD),'YYYYMMDD') AS "YYMD ts", 03 DATE(TIMESTAMP_FORMAT(CHAR(YYMD),'YYYYMMDD')) AS "YYMD date", 04 DATE(TIMESTAMP_FORMAT(CHAR(YYMD),'YYYYMMDD')) - 35 DAYS AS "YYMD - 35", 05 DEC(DATE(TIMESTAMP_FORMAT(CHAR(YYMD),'YYYYMMDD')) - 35 DAYS) AS "YYMD nbr" 06 FROM MYLIB.TESTFILE

Line 1: This is the number from the file.

Line 2: This is that number converted to a timestamp. I first have to convert the number to a character string. Then I use the TIMESTAMP_FORMAT function to convert that character string into a timestamp. As the character string does not contain a date separators I have to tell the function what format the "date" is in.

Line 3: I use the DATE function to convert the timestamp value to a date.

Line 4: Dates so easy to use. All I do is subtract 35 days from the calculated date.

Line 5: All I have to do is use the DEC function to convert the date back to a decimal value.

The results of this are:

YYMD YYMD ts YYMMD date YYMD - 35 YYMD nbr -------- -------------------------- ---------- ---------- -------- 20200518 2020-05-18 00:00:00.000000 2020-05-18 2020-04-13 20200413 20200312 2020-03-12 00:00:00.000000 2020-03-12 2020-02-06 20200206 20190222 2019-02-22 00:00:00.000000 2019-02-22 2019-01-18 20190118

As I have determined the calculation I need to use I can now update the field in the file:

UPDATE MYLIB.TESTFILE SET YYMD = DEC(DATE(TIMESTAMP_FORMAT(CHAR(YYMD),'YYYYMMDD')) - 35 DAYS)

When I look at the records in the file I can see that the number matches my test data:

YYMD ---------- 20,200,413 20,200,206 20,190,118

 

CYYMMDD field

This is a little more complex as the "date" has only one number to denote the century:

• "0XXXXXX" = 1900 - 1999
• "1XXXXXX" = 2000 – 2099

This is the test SQL statement I built for testing my logic with this field:

01 SELECT CYMD, 02 DATE(TIMESTAMP_FORMAT(CHAR(19000000 + CYMD),'YYYYMMDD')) AS "CYMD date", 03 DATE(TIMESTAMP_FORMAT(CHAR(19000000 + CYMD),'YYYYMMDD')) - 35 DAYS AS "CYMD - 35", 04 DEC(DATE(TIMESTAMP_FORMAT(CHAR(19000000 + CYMD),'YYYYMMDD')) - 35 DAYS) - 19000000 AS "CYMD nbr" 05 FROM MYLIB.TESTFILE

Line1: This is the number from the file.

Line 2: It gets a little complicated when converting this numeric representation of a date to a timestamp. The timestamp function cannot translate a CYYMMDD directly, I need to add 19000000 to the number field to make it a YYYYMMDD value that can then be converted to a timestamp, and then to a date.

Line 3: As before I subtract 35 days from the date.

Line 4: When I use the DEC function to convert this date to a decimal value returns an eight long date in YYYYMMDD format. I need to subtract 19000000 from it to convert it to CYYMMDD format.

The test for this type of number looks like:

CYMD CYMMD date CYMD - 35 CYMD nbr -------- ---------- ---------- -------- 1200518 2020-05-18 2020-04-13 1200413 1200312 2020-03-12 2020-02-06 1200206 1190222 2019-02-22 2019-01-18 1190118

And now to update the file:

UPDATE MYLIB.TESTFILE SET CYMD = DEC(DATE(TIMESTAMP_FORMAT(CHAR(19000000 + CYMD), 'YYYYMMDD')) - 35 DAYS) - 19000000

The data from the file shows that the values in the fields has been changed:

CYMD --------- 1,200,413 1,200,206 1,190,118

 

This shows how easy it is to use SQL for doing date math, even when the "date" is really a number.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.