| Comparing dates and times [message #370523] |
Wed, 15 December 1999 11:09  |
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  |
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
