Some time ago, I discussed the never-ending problem of converting a six digit number into an ISO date. I have since found a better way of doing this and, in order to save myself a search through the IBM Knowledge Center every time I need to do this, I’m putting it up here.
So here’s the problem: I have a table in which the date is represented as a 7 digit number. The first digit is the century (0 indicates 20th century, 1 indicates 21st century) and the next six digits represent the date in YYMMDD format. So 1150831, for example, is 31st August 2015.
The request in hand is for a report, so I want to convert the number into a date so that it looks sensible once exported into Excel. Timestamp_format is your friend.
The TIMESTAMP_FORMAT function returns a timestamp that is based on the interpretation of the input string using the specified format.
It’s that “interpretation of the input string” that makes this so handy. Put simply: you tell the function how to interpret the string and it will do the rest.
Here’s an example. I have a contracts table (CONTRACTS) with three columns: Contract Number (CTNR), Start Date (STDT) and End Date (ENDT). The start and end date are both seven digit numbers as described previously. I need to list all of the currently active contracts (Start Date is before Today and End Date is after Today).
And the SQL looks like this:
select CTNR, date(timestamp_format(substr(digits(STDT), 2, 6), 'YYMMDD')), date(timestamp_format(substr(digits(ENDT), 2, 6), 'YYMMDD')) from CONTRACTS where date(timestamp_format(substr(digits(STDT), 2, 6), 'YYMMDD')) <= current_date and date(timestamp_format(substr(digits(ENDT), 2, 6), 'YYMMDD')) >= current_date
Current_date is a special register that returns the current date, but that’s not important right now.
The date conversion part involves several nested functions to get to a final date, so here’s the breakdown using a date of 1st September 2015 (1150901):
- digits(STDT) converts the 7 digit number into a 7 character string. So 1150901 is converted to ‘1150901’.
- The IBM i can figure out which century it’s in by looking at the year, so I don’t need the century flag and the substr(digits(STDT), 2, 6) strips it out to give me a value of ‘150901’
- The timestamp_format function takes the date string and uses the format string of ‘YYMMDD’ to generate a timestamp of 2015-09-01-00.00.00.000000. Since I only passed it a date, the hours, minutes and seconds are all zeroes.
- And finally, I can use the date function to retrieve the date from the generated timestamp.
And here’s the result:
CTNR DATE DATE C1000001 01/09/15 31/08/16
Pretty, and portable.