As regards your question about sutracting dates, there is an easy way to do
If you have two columns in a table say col1 (the later datetime) and col2
(the earlier datetime), both defined as DATE column formats (populated by
sysdate at some point).
To find the difference between col1 and col2 you can use a format mask with
the TO_CHAR function and the inherent properties of SQL DATE format to get
back in any format you want - let SQL do most of the conversion.
The way that ORACLE DATEs work is, that if you subtract two dates you end
up with an element that is a proportion of 24hrs. However, if you add back
on to the result a truncated sysdate (ie. with the time element removed),
you can treat the result as a DATE, (you can effectively ignore the date
element that's been added back on by using format masks that only look at
the time element) so you can therefore use ORACLE's TO_CHAR date conversion
functionality, with defined format masks, to get results in the format you
To get the date (col1-col2) difference in HHMI format:
SELECT TO_CHAR(TRUNC(sysdate)+(col1-col2),'HHMI') FROM ....
or to get the equivalent in minutes, (converting the Hours to Minutes):
FROM . . . .
Please email me if this is unclear.
Senior ORACLE Analyst
> I've not done this for some time, and am having a hard time coming up
> with a good example. Has anyone got something similar to what my
> friend has asked for? When he asked I relalized I have probably not
> needed to do this sort of thing for 5 years or more.
> I have not done a lot of SQL in my day and thought maybe you would
> have a good example for me. I have looked in several books & scripts
> but cannot find and example. I have two date columns in a table both
> populated by sysdate at some point. I want to find the difference
> between them in HHMI and MI. Do you have and example you could share
> with me?
> Michael Alan Kline, Sr.