Home » RDBMS Server » Server Administration » Comparing dates and times
Comparing dates and times [message #370523] Wed, 15 December 1999 11:09 Go to next message
Chris
Messages: 128
Registered: November 1998
Senior Member
I've got two dates and ultimately I'd like to know the difference in minutes between the two. How is this done in SQL for Oracle.

(ex: (DATE1= 11/6/99 12:01:43 AM) -
(DATE2 = 11/6/99 3:48:13 AM)

Thank you.
Re: Comparing dates and times [message #370524 is a reply to message #370523] Wed, 15 December 1999 11:27 Go to previous messageGo to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
Chris,
If you select most_recent_date - least_recent_date from your_table, the returned value will be in days and fractional days, so to convert to minutes multiply the result by 1440. Thus:
SELECT ROUND((date2 - date1) * 1440) from your_table would give you the result rounded to the nearest minute.
Hope this helps,
Paul
Re: Comparing dates and times [message #370529 is a reply to message #370523] Thu, 16 December 1999 08:52 Go to previous messageGo to next message
Chris
Messages: 128
Registered: November 1998
Senior Member
Hi Paul,

The solution you gave me yesterday worked beautifully, thank you. My other issue now is summing the result.
This is the select statement I have now. How do I sum it or group it?

SELECT ORG.ORG_NAME, ROUND(((ACCT_BACKUP.CREATED - ACCT_BACKUP.STARTED) * 1440),2)
from ORG, ACCT_BACKUP



Thank You

Chris
Re: Comparing dates and times [message #370551 is a reply to message #370529] Sat, 18 December 1999 07:26 Go to previous message
Paul
Messages: 164
Registered: April 1999
Senior Member
Chris,
Probably the simplest way is to write a SQL*Plus Report as follows:

ttitle 'Time by Organization'

clear breaks
clear computes

break on report on NAME skip 1
compute sum of TIME on NAME report

SELECT ORG.ORG_NAME as NAME,
ROUND(((ACCT_BACKUP.CREATED - ACCT_BACKUP.STARTED) * 1440),2) as TIME
from ORG, ACCT_BACKUP
order by NAME;

This will give you a subtotal for each oraganization and a grand total for the entire report. You can do a lot more output formatting with this, I'm just showing you the bare bones. If you or your company can afford it, the Oracle Press book 'ORACLE_ The Complete Reference' for your version of Oracle is a very worthwhile investment. Also, if you have not already done so, go to technet.oracle.com and join OTN, it's free and gives you on-line access to a wealth of documentation and sample code.
Hope this helps,
Paul
Previous Topic: How to get a table's fields and their types in PL/SQL?
Next Topic: Grouping or summing an aggregate function
Goto Forum:
  


Current Time: Thu Oct 22 04:07:40 CDT 2020