Home » SQL & PL/SQL » SQL & PL/SQL » datatype conversion --- INTERVAL DAY(9) TO SECOND(6) ---> HH24:MI (Oracle 11.2.0.4/ Redhad 6.3)
datatype conversion --- INTERVAL DAY(9) TO SECOND(6) ---> HH24:MI [message #634239] Fri, 06 March 2015 04:33 Go to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member

Hi, Any one please help me in writing select query.

From dba_scheduler_jobs I want below column in HH24:MI format.

LAST_RUN_DURATION --datatype is --> INTERVAL DAY(9) TO SECOND(6)

My Query
========
select LAST_RUN_DURATION from dba_scheduler_jobs;

This output time is entirely different from OEM--> <db> -->Administration --> oracle scheduler -->Jobs --> <job run duration>


Regards
sbmk

Report message to a moderator

Re: datatype conversion --- INTERVAL DAY(9) TO SECOND(6) ---> HH24:MI [message #634245 is a reply to message #634239] Fri, 06 March 2015 06:43 Go to previous message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
TO_CHAR(TRUNC(SYSDATE) + LAST_RUN_DURATION,'HH24:MI') assuming duration is < 24 hours, otherwise use EXTRACT function.

SY.

Report message to a moderator

Previous Topic: Happy 2015 my dear friends! :D
Next Topic: How to use package dbms_sqlhash without privileges?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri May 10 07:22:43 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.