Time function - Oracle

[praveenmittal]

Dear Friends,

I need to know the following:

I will provide a numeric value say 610, and this value has to be converted into respective hours, minutes and second format ie 10 hr 10 min 00 sec.

How can I achieve this. Please provide a query or some guidelines to do it.

Regards

Praveen

[amitpatel66]

Check out below code:

DECLARE
a NUMBER := &1;
hours NUMBER;
minu NUMBER;
BEGIN
SELECT TRUNC(a/60) INTO hours FROM dual;
SELECT MOD(a,60) INTO minu FROM dual;
DBMS_OUTPUT.PUT_LINE (hours||' HRS '||minu||' MINS'||' 00 SECS');
END;
/

The above code will work on multiples of 60. If the MOD(number,60) is lesser than 60, then that value will be considered as MINUTES. Here the seconds will always be ZERO. I hope this is what u require.

It would be good if u can be more specific on your requirement.
R u gonna paas only 3 digit number always or more than that?
The above code accepts any digit numbers.

[debasisdas]

Since u need to return multiple values ,its better to write a procedure with one IN mode parameter and 3 OUT mode parameter.