Converting numbers and characters into timestamps

I am sure we have all worked with ERP Applications that still store their dates and times in numeric or character fields. I was asked what would need to be done to convert these into a timestamp in a SQL Select statement. Personally, I do like using timestamps rather than individual date and time columns, or fields. Therefore, I consider this a good question.

I have a SQL DDL Table with a mixture of "date" and "time" columns within it:

• DATE_NBR8:  A numeric representation of a date, eight long, and in ISO format (YYYYMMDD).
• DATE_NBR7:  A numeric representation of a date, seven long, in CYMD format (CYYMMDD).
• DATE_CHAR:  A character representation of a date, eight long, in ISO format with no separator characters.
• DATE_DATE:  What I would call a "true date", date data type.
• TIME_NBR:  A numeric representation of a time, six long.
• TIME_CHAR:  A character representation of a time, six long, with no separator characters.
• TIME_TIME:  A "true time", time data type.

Let me start with the simplest scenario: combining "true date" and "true time" to make a timestamp:

01 SELECT DATE_DATE,TIME_TIME, 02 TIMESTAMP(DATE_DATE,TIME_TIME) AS "Timestamp" 03 FROM TESTTABLE

Line 2: This shows how simple it is to create a timestamp. I use the TIMESTAMP scalar function, and pass it the date and the time to combine.

The results are:

DATE_DATE TIME_TIME Timestamp ---------- --------- -------------------------- 2024-02-15 17.15.22 2024-02-15 17:15:22.000000

Combining "true" date and time is easy, and not worth a post on its own. It gets more interesting when I combine numeric and character representations of dates and times.

The next example uses the eight long numeric "date" and six long numeric "time":

01 SELECT DATE_NBR8,TIME_NBR, 02 DATE(TIMESTAMP_FORMAT(CHAR(DATE_NBR8),'YYYYMMDD')) AS "Date", 03 TIME('00:00:00') + TIME_NBR AS "Time", 04 TIMESTAMP(CHAR(DATE(TIMESTAMP_FORMAT(CHAR(DATE_NBR8),'YYYYMMDD'))), TIME('00:00:00') + TIME_NBR) AS "Timestamp" 06 FROM TESTTABLE

I added a couple of columns to this statement: Date and Time, this allows me to verify that I converted the numeric representation of the date and time to a valid date and time.

Line 2: To convert a number to a date I need to:

• Use CHAR scalar function to convert the numeric value to character
• TIMESTAMP_FORMAT scalar function with the first parameter being the character value, and the second being the format of the "date" in that character
• Finally the DATE scalar function to convert the timestamp to a "true" date

Line 3: The conversion of the numeric "time" to a "true" time is much simpler. I have the TIME scalar function with the time of all zeroes, and I just add my numeric "time" to it.

Line 4: This line wraps to the line below. All I am doing here is using the TIMESTAMP scalar function to combine the date created, made in the same way as line 2, and the time, made in line 3.

The results are:

DATE_NBR8 TIME_NBR Date Time Timestamp --------- -------- ---------- -------- -------------------------- 208808 111023 2088-08-08 11.10.23 2088-08-08 11:10:23.000000

Next example is with the seven long numeric representation of the date:

01 SELECT DATE_NBR7,TIME_NBR, 02 DATE(TIMESTAMP_FORMAT(CHAR(DATE_NBR7 + 19000000),'YYYYMMDD')) AS "Date", 03 TIME('00:00:00') + TIME_NBR AS "Time", 04 TIMESTAMP(CHAR(DATE(TIMESTAMP_FORMAT(CHAR(DATE_NBR7 + 19000000), 'YYYYMMDD'))), TIME('00:00:00') + TIME_NBR) AS "Timestamp" 05 FROM TESTTABLE

Line 2: As the date is CYMD I need to add 19,000,000 (1900-00-00) to go from '1YYMMDD' to '20YYMMDD'. Otherwise the line is the same as the second line in the previous statement.

Line 4: I need to add 19 million to the date as I did in line 2.

The results are:

DATE_NBR7 TIME_NBR Date Time Timestamp --------- -------- ---------- -------- -------------------------- 1770707 111023 2077-07-07 11.10.23 2077-07-07 11:10:23.000000

In the final example both the "date" and "time" are character values. My statement looks similar to the two previous:

01 SELECT DATE_CHAR,TIME_CHAR, 02 DATE(TIMESTAMP_FORMAT(DATE_CHAR,'YYYYMMDD')) AS "Date", 03 TIME('00:00:00') + DEC(TIME_CHAR,6,0) AS "Time", 04 TIMESTAMP(CHAR(DATE(TIMESTAMP_FORMAT(DATE_CHAR,'YYYYMMDD'))), TIME('00:00:00') + DEC(TIME_CHAR,6,0)) AS "Timestamp" 05 FROM TESTTABLE

Line 2: As DATE_CHAR is character I do not need to convert it to character in the TIMESTAMP_FORMAT scalar function.

Line 3: I do need to convert TIME_CHAR to a decimal value, then I add it to the TIME scalar function.

Line 4: The same changes I made in lines 2 and 3 are used in this line too.

The results are:

DATE_CHAR TIME_CHAR Date Time Timestamp --------- --------- ---------- -------- -------------------------- 20240515 022333 2024-05-15 02.23.33 2024-05-15 02:23:33.000000

As you have seen it is simple to create a timestamp from numeric and character representations of dates and times. There is a danger here as if any of the numeric or character columns contain an invalid "date" or "time", for example: zeroes, this statements will error.

 

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