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
|
|
|
|
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
|
|
|
|
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.
|
|
|