RE: Convert SQL time duration to character -- MIDRANGE-L

Tommy,

The VARCHAR worked, converted the decimal number to a character, getting rid of the comma.

SELECT substr(FLASTR,69,8) as "Date" , substr(FLASTR,31,34) as
"Start of Job" , substr(FLASTR,78,11) as " Time" ,
substr(FLAend,58,29) as "End of Job" , VARCHAR_FORMAT
((time(substr(flaend,79,8)) - time(substr(flastr,81,8)))) as HMMSS
, substr(flaend,89,5) as "Total CPU" FROM qgpl.flastr JOIN
qgpl.flaend on substr(FLASTR,31,26) = substr(flaend,31,26) WHERE
substr(FLASTR,38,3) = 'FLA' and substr(FLASTR,47,4) = 'QPAD' ORDER
BY substr(FLASTR,69,8)

"Date" "Start of Job" " Time" "End of Job" HMMSS "Total CPU"
04/01/17 793376/FLACWILS/QPADEV003Z started at 08:56:27 ended on 04/01/17 at 17:30:14 83347 6.299
04/01/17 793377/FLAAKHAL/QPADEV0040 started at 08:56:39 ended on 04/01/17 at 17:30:14 83335 4.402
04/01/17 793387/FLAGBRIG/QPADEV004F started at 08:59:00 ended on 04/01/17 at 17:30:07 83107 7.284
04/01/17 794176/FLAHDOUG/QPADEV005N started at 10:26:31 ended on 04/01/17 at 19:00:10 83339 4.881

Paul

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tommy Holden
Sent: Tuesday, April 25, 2017 5:54 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Convert SQL time duration to character

Not near a system but I believe that varchar_format is what you are looking for

Sent from my Verizon Wireless 4G LTE Droid On Apr 25, 2017 4:33 PM, "Steinmetz, Paul" <PSteinmetz@xxxxxxxxxx> wrote:
I need to convert an SQL time duration to character.

(time(substr(flaend,79,8)) - time(substr(flastr,81,8)))

Which function should I use?
I took a look at CAST, no success as of yet.

Thank You
_____
Paul Steinmetz
IBM i Systems Administrator

Pencor Services, Inc.
462 Delaware Ave
Palmerton Pa 18071

610-826-9117 work
610-826-9188 fax
610-349-0913 cell
610-377-6012 home

psteinmetz@xxxxxxxxxx<mailto:psteinmetz@xxxxxxxxxx>
http://www.pencor.com/

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD