Bryan,
There is no direct way to convert a numeric value into a date time representation. If you only want a character representation, you could try something like this:
Translate(VarChar_Format(Dec(Timestamp1 - Timestamp2, 10, 0), '00,00,00,00'), ':', ',')
Note: VarChar_Format is normally used for to convert timestamps into character representation or to convert numeric values into a character representation.
Since the number of digits between the group separator (,) is not checked you could use it to build a character representation of a date/time value. But the group separator is always a comma, so if you need a colon, you have to convert it (with either the TRANSLATE or REPLACE function).
If you only deal with times, you could also try the following (the result is a real time!)
Time('00.00.00') + (Midnight_Seconds(Time1) - Midnight_Seconds(Time2)) Seconds
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization – Education – Consulting on IBM i
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Bryan Dietz
Sent: Wednesday, 13 September 2023 22:35
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Data Subtraction question
Birgitta, as a one off to your comment.
I've used the subtraction date but have not been able to find a decent way to edit the resulting value that I have for calculating the wall clock time for job:
digits(dec(t3.MESSAGE_TIMESTAMP - t2.MESSAGE_TIMESTAMP)) as Duration_ddhhmmss
into something like dd:hh:mm:ss
Bryan
Birgitta Hauser wrote on 9/13/2023 12:15 AM:
If you subtract 2 dates with SQL the result will be an 8 digit numeric
value representing the difference in Years, months, days. i.e. in your
example 103 means 1 Month and 3 Days.
Birgitta Hauser
Modernization – Education – Consulting on IBM i
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
midrange.com
