Subtracting Dates

[MicahDB]

I'm trying to get an "order age time" based on SYSDATE and INSERT_DTM. Both fields, without any kind of data manipulation applied, are in the 2004-09-29 14:18:33 format. I'm want the results to show in minutes.

Here's what I've tried:

SELECT
to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss') - to_char(INSERT_DTM,'yyyy-mm-dd hh24:mi:ss')
Result - ORA-01722:INVALID NUMBER

to_char(to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss') - to_char(INSERT_DTM,'yyyy-mm-dd hh24:mi:ss'),'MI')
Result - ORA-01722:INVALID NUMBER

(to_char(to_date(SYSDATE,'yyyy-mm-dd hh24:mi:ss') - to_date(INSERT_DTM,'yyyy-mm-dd hh24:mi:ss'),'MI'))
Result - I actually get results with this, but it's "#".

(to_char(to_date(INSERT_DTM,'yyyy-mm-dd hh24:mi:ss') - to_date(SYSDATE,'yyyy-mm-dd hh24:mi:ss')))
Results - I get results but it's not subtracting correctly.
ex. 2004-09-30 09:11:08 - 2004-10-01 00:28:00 = "10562"

That's not right...

Please help!
Thanks,
Micah

 

[SantaMufasa]

Micah,

Try this:

col a heading "Minutes|Since|Insert_DTm" format 999,999,999.9
col b heading "Insert_DTm"
select to_char(insert_dtm,'yyyy-mm-ss hh24:mi:ss') b
     ,(sysdate-insert_dtm)*(1*24*60) a from micah;

                           Minutes
                             Since
Insert_DTm              Insert_DTm
------------------- --------------
2004-09-26 07:58:26        3,608.8
2004-09-12 05:06:12       18,181.0

2 rows selected.

Mufasa
(aka Dave of Sandy, Utah, USA @ 03:09 (01Oct04) UTC (aka "GMT" and "Zulu"), 20:09 (30Sep04) Mountain Time)