Convert numbers to times and then into timestamps

I am sure that most of us work with ERP databases where the date and time fields are really numbers. Finding ways to convert these numbers into "real" dates and times allows us to make use of all the rich date and time features within the IBM i operating system.

I have described in a previous post how to convert various numeric versions of a date to a real date. So I won't bother to repeat that.

But what about converting numbers into a real time?

Before I start giving examples let me show the file that contains the data I will be using. Yes, I am using a file as in my experience if there are numbers pretending to be dates and times then they are in DDS files, rather than DDL Tables. The file contains four fields:

A..........T.Name++++++RLen++TDpB......Functions+++++++++++ A R TESTFILER A STRDATE 7P 0 A STRTIME 6P 0 A ENDDATE R REFFLD(STRDATE *SRC) A ENDTIME R REFFLD(STRTIME *SRC)

I don't think I need to explain what these fields contain as their names adequately explain their function.

As this is only an example the file contains two records:

01 SELECT * FROM TESTFILE ; STRDATE STRTIME ENDDATE ENDTIME ------- ------- ------- ------- 920630 110000 1210101 0 1201231 45322 1210125 136060

The dates are in CYMD format, and the times are in 24 hours format. Notice that the End Time for the second record is not a valid time.

The first step is to convert the date fields to a "real" date, and the time fields to a "real" time.

01 SELECT STRDATE, 02 DATE(TIMESTAMP_FORMAT(CHAR(19000000 + STRDATE),'YYYYMMDD')) AS "Date", 03 STRTIME, 04 TIME('00:00:00') + STRTIME AS "Time", 05 FROM TESTFILE ;

Line 2: This is how to convert a number pretending to be a CYMD date to a "real" date.

Line 4: I add the number pretending to be a time to a real time field of zero. The result will be the "real" time of the number.

The results are:

STRDATE Date STRTIME Time ------- ---------- ------- -------- 920630 1992-06-30 110000 11.00.00 1201231 2020-12-31 45322 04.53.22

What happens if I do the same with that invalid time in the End time field?

01 SELECT ENDDATE, 02 DATE(TIMESTAMP_FORMAT(CHAR(19000000 + ENDDATE),'YYYYMMDD')) AS "Date", 03 ENDTIME, 04 TIME('00:00:00') + ENDTIME AS "Time" 05 FROM TESTFILE ;

The results did surprise me:

STRDATE Date STRTIME Time ------- ---------- ------- -------- 1210101 2021-01-01 0 00.00.00 1210125 2021-01-25 136060 14.01.00

The surprise is the number 136060, which is an invalid time, was converted to 14.01.00!

As I have a "real" dates and times I can make a timestamp from them.

The basic syntax for doing this is:

01 SELECT 02 TIMESTAMP(CHAR(< date >),CHAR(< time >)) 03 FROM < file > ;

Line 2: The TIMESTAMP function combines character representations of the date and time into a timestamp. Notice how they are separated by a comma.

In my example I need to convert the numeric date and time fields into dates and times before I can combine them into a timestamp. My statement would look like:

01 SELECT 02 TIMESTAMP( 03 CHAR(DATE(TIMESTAMP_FORMAT(CHAR(19000000 + STRDATE), 'YYYYMMDD'))), 04 CHAR(TIME('00:00:00') + STRTIME)) AS "Start Timestamp", 05 TIMESTAMP( 06 CHAR(DATE(TIMESTAMP_FORMAT(CHAR(19000000 + ENDDATE), 'YYYYMMDD'))), 07 CHAR(TIME('00:00:00') + ENDTIME)) AS "End Timestamp" 08 FROM TESTFILE ;

Line 2 and 5: The TIMESTAMP function starts here, and ends with the last parentheses ( ) ) on lines 4 and 7.

Line 3 and 6: Convert the number to a date, and then convert the result to a character literal. Notice how this line ends with a comma this is used to separate the date from the time.

Line 4 and 7: This converts the date I created by the method I described before, into a character literal.

The TIMESTAMP has combined the two into a timestamp type column, see below:

Start Timestamp End Timestamp -------------------------- -------------------------- 1992-06-30 11:00:00.000000 2021-01-01 00:00:00.000000 2020-12-31 04:53:22.000000 2021-01-25 14:01:00.000000

Now I have these timestamps I can start using them for all kinds of things, that I could not when they were separate numbers.

 

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