Convert datediff hrs into hrs, min & sec

From: <shweta.kaparwan_at_googlemail.com>
Date: Mon, 30 Mar 2009 14:25:39 -0700 (PDT)
Message-ID: <713f56d4-0fdb-42b2-970d-1528f4c0c707_at_e2g2000vbe.googlegroups.com>



Hi All,

I have :

XX_at_MYDB9I:XXXX> desc t1;  

Name
Null? Type  


  • --------------------------------------

OBJECT_NAME
VARCHAR2(128)   CREATED
DATE   LAST_DDL_TIME
DATE
 DAY_LIGHT_SAVING_IN_SEC
NUMBER XX_at_MYDB9I:XXXX>

XX_at_MYDB9I:XXXX> select * from t1;

OBJECT_NAME                    CREATED             LAST_DDL_TIME
DAY_LIGHT_SAVING_IN_SEC
  • -------------------
    RUNSTATS_PKG 28/03/2009 00:00:00 29/03/2009 03:59:59 3600 C_OBJ# 28/03/2008 00:00:00 29/03/2008 12:30:30 3600

Now i want time difference in hrs between last_dd_time & created time :

so i did :

XX_at_MYDB9I:XXXX> select object_name, (last_ddl_time-created)*24 "DDL_AFTER_HRS" from t1;

OBJECT_NAME                    DDL_AFTER_HRS
------------------------------              -------------
RUNSTATS_PKG                   3.99972222
C_OBJ#                                12.5083333

XX_at_MYDB9I:XXXX>

Now I have a requirement to convert these hrs ( "DDL_AFTER_HRS" ) into hrs, mins & sec
i.e.
i want
3.99972222 hrs to be converted into 3 hr 59 min & mins sec &
12.5083333 hrs to 12 hr 30 min & 30 sec

how to do this?.

Regards

Shweta Received on Mon Mar 30 2009 - 16:25:39 CDT

Original text of this message