Wednesday, July 22, 2020

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

substract 35 days from a date when it is a packed numeric field

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.

6 comments:

  1. If you explain the use of TIMESTAMP_FORMAT() it makes the statement complicated. If you want a better performing statement, you can convert numeric YYYYMMDD dates using this syntax form DATE( DIGITS( YYYYMMDD ) concat '000000' ).

    -Matt

    ReplyDelete
  2. Our main application database has dates in other formats, such as long Julian (yyyyddd) or even ddmmyy (don't start me). To that end, I have created an SQL function which via some simple RPG converts dates to/from many more formats with or without separators. I'd also observe that the year range for 0/1 for cyymmdd dates is not consistently those above. Even with the operating system the meaning is different (look at CVTDAT for instance...)

    ReplyDelete
    Replies
    1. I agree sometimes creating your own UDF and handling date conversions using RPG is easier. I have an example here.

      Delete
  3. Our database is Numeric CYYMMDD fields for dates. TO go back 35 days, we use

    decimal(replace(char((current date-35 Days), ISO),'-','') -19000000,7,0)

    ReplyDelete
  4. In this example we've assume fixed 35 days. however, can we do it if my days to be subtracted are variable? Days to subtract is a field of the file and may vary.

    ReplyDelete
    Replies
    1. If you want to parameterize the number of days you can do something like this:

      dcl-s WorkDate date ;
      dcl-s NumberOfDays int(3:0) ;

      NumberOfDays = 10 ;
      exec sql SET :WorkDate = CURRENT_DATE - :NumberOfDays DAYS ;

      By all means I used a SET rather than a SELECT, but the logic is the same.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.