Date time operations

[jcasas23]

How could I do math operations with datatime type variables?

I want to do the next: date format MM/DD/YY HH24:MI

date1 = '07/01/04 03:03'
date2 = '07/01/04 16:30'

I just want to query rows where date1 > date2

Any advice?

thanks in advance

 

[LKBrwnDBA]

Try:

Select * From MyTable
 Where TO_DATE(date1,'MM/DD/RR HH24:MI') 
     > TO_DATE(date2,'MM/DD/RR HH24:MI');

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[jcasas23]

thanks very useful, and if I want to know the minutes between 2 times.

date1 = '07/01/04 03:03'
date2 = '07/01/04 16:30'

result = date1 - date2

thanks in advance

 

[SantaMufasa]

J,

Here is some sample code:

var date1 char(14)
var date2 char(14)
exec :date1:='07/01/04 03:03'
exec :date2:='07/01/04 16:30'
col a heading "Date 1" format a20
col b heading "Date 2" format a20
col c heading "Minutes|Difference" format 99,999
col d heading "Seconds|Difference" format 99,999
select to_char(TO_DATE(:date1,'MM/DD/RR HH24:MI'),'dd-mon-yyyy hh24:mi:ss')a
      ,to_char(TO_DATE(:date2,'MM/DD/RR HH24:MI'),'dd-mon-yyyy hh24:mi:ss')b
      ,(TO_DATE(:date2,'MM/DD/RR HH24:MI')-TO_DATE(:date1,'MM/DD/RR HH24:MI'))
           *(24*60)c    -- For minutes
      ,(TO_DATE(:date2,'MM/DD/RR HH24:MI')-TO_DATE(:date1,'MM/DD/RR HH24:MI'))
           *(24*60*60)d -- For seconds
from dual
/

                                             Minutes    Seconds
Date 1               Date 2               Difference Difference
-------------------- -------------------- ---------- ----------
01-jul-2004 03:03:00 01-jul-2004 16:30:00        807     48,420

Let us know if this answers your questions.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[jcasas23]

Santa Mufasa

Your script was perfect, that's the information that I was looking for.

thanks