Re: Convert datediff hrs into hrs, min & sec

From: <engel.kevin_at_gmail.com>
Date: Mon, 30 Mar 2009 19:00:48 -0700 (PDT)
Message-ID: <4c250a14-2513-4357-b7d6-ef7a01f19e8f_at_y13g2000yqn.googlegroups.com>

On Mar 30, 6:09 pm, johnbhur..._at_sbcglobal.net wrote:
> On Mar 30, 5:25 pm, shweta.kapar..._at_googlemail.com wrote:
>
>
>
> > 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
>
> Read the oracle documentation?

So give the poor guy a hint...
Look up the INTERVAL datatype in the SQL Reference manual. There are at least a couple of ways that it might apply in your situation, but you'll need to do some research to figure out which one(s) makes sense. Received on Mon Mar 30 2009 - 21:00:48 CDT