OraFAQ Forum: SQL & PL/SQL » Date Arithematic Readable format

Home » SQL & PL/SQL » SQL & PL/SQL » Date Arithematic Readable format

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date Arithematic Readable format [message #170511]

Thu, 04 May 2006 02:35

ksoneka
Messages: 2
Registered: April 2006

Junior Member

Hi,

I want to find difference between two datetime fields in mins:secs.

When I am trying the following query, the output is of format "+000000000 00:02:39.245744".

select JOB_END_TS - JOB_START_TS TIME 
from job_list

Is the output an Interval type or a Float type.

Please also help me how to display only in mins:secs, i.e. removing all unnecessary zeroes.

Thanks

Report message to a moderator

Re: Date Arithematic Readable format [message #170655 is a reply to message #170511]

Thu, 04 May 2006 11:47

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

http://asktom.oracle.com/~tkyte/Misc/DateDiff.html

Report message to a moderator

Re: Date Arithematic Readable format [message #170777 is a reply to message #170655]

Fri, 05 May 2006 02:29

ksoneka
Messages: 2
Registered: April 2006

Junior Member

Hi,

I have tried the follwoing query.

select job_nm,
mod( (job_end_ts - job_start_ts)*24, 24 ) )  "Hr", 
mod( (job_end_ts - job_start_ts)*24*60, 60 ) )  "Mi"
from job_hist

But it is giving error as

ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL

Please HELP

Report message to a moderator

Re: Date Arithematic Readable format [message #170879 is a reply to message #170511]

Fri, 05 May 2006 13:06

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Try the following.

select to_char(JOB_END_TS - JOB_START_TS TIME,'HH:MI:SS.FFFFFF')
from job_list

Report message to a moderator

Re: Date Arithematic Readable format [message #170973 is a reply to message #170777]

Sat, 06 May 2006 17:29

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

MOD() takes numeric parameters.

You can get most of the way there with CAST:

SQL> WITH ts AS
  2  ( SELECT TIMESTAMP '2000-01-01 00:00:00' AS job_start_ts
  3         , TIMESTAMP '2000-01-01 00:02:39.245744' AS job_end_ts
  4    FROM   dual )
  5  SELECT CAST(job_end_ts - job_start_ts AS INTERVAL DAY(0) TO SECOND(0)) AS elapsed
  6  FROM ts;

ELAPSED
---------------------------------------------------------------------------
+0 00:02:39

1 row selected.

Report message to a moderator

Previous Topic:	cluster & noncluster
Next Topic:	Query output from two tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 29 23:43:06 CDT 2024