Home » SQL & PL/SQL » SQL & PL/SQL » Date Arithematic Readable format
Date Arithematic Readable format [message #170511] Thu, 04 May 2006 02:35 Go to next message
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
Re: Date Arithematic Readable format [message #170655 is a reply to message #170511] Thu, 04 May 2006 11:47 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
http://asktom.oracle.com/~tkyte/Misc/DateDiff.html
Re: Date Arithematic Readable format [message #170777 is a reply to message #170655] Fri, 05 May 2006 02:29 Go to previous messageGo to next message
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
Re: Date Arithematic Readable format [message #170879 is a reply to message #170511] Fri, 05 May 2006 13:06 Go to previous messageGo to next message
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
Re: Date Arithematic Readable format [message #170973 is a reply to message #170777] Sat, 06 May 2006 17:29 Go to previous message
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.
Previous Topic: cluster & noncluster
Next Topic: Query output from two tables
Goto Forum:
  


Current Time: Fri Nov 22 10:18:37 CST 2024