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,
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_TIMEDAY_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