Subtracting dates

Subtracting dates

Post by Michael Alan Kline, S » Tue, 04 Mar 1997 04:00:00



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.


Mike,
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?

Thanks

Michael Alan Kline, Sr.
800-933-7668

 
 
 

Subtracting dates

Post by Karl M. Nordquis » Thu, 06 Mar 1997 04:00:00


I think if you subtract the two columns (like in a select stmt) you will
get a decimal number.  This decimal number is the number of days
difference.  If you multiply by 24 to get hours, take the decimal
portion of that (or all of it if not an whole hour) and multiply by 60
to get minutes, etc....

HTH

Regards

//Karl

--
====================================================

Application Development
Bright Wood Corporation
Madras, Oregon
====================================================

 
 
 

Subtracting dates

Post by Martyn Cavet » Fri, 07 Mar 1997 04:00:00


Michael -

As regards your question about sutracting dates, there is an easy way to do
this.

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
the data
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
want.

For example:

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):

SELECT 60*TO_NUMBER(TO_CHAR(TRUNC(sysdate)+(col1-col2),'HH'))+
         TO_NUMBER(TO_CHAR(TRUNC(sysdate)+(col1-col2),'MI'))
FROM . . . .

Please email me if this is unclear.

Regards

Martyn Cavett
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.


> Mike,
> 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?

> Thanks

> Michael Alan Kline, Sr.
> 800-933-7668


 
 
 

1. Subtracting dates

I am new to Analysis Services.  I can create a cube showing sales per
date by region etc.  But I am having problem getting the Average time
between purchases.

Here is my problem:

Customer Table
--------------
CustID
------
1
2
3
4
5

CustomerSales Table
-------------------
CustID    SaleDate
------    --------
1         1/1/2001
1         2/1/2002
1         3/2/2002

Can you help me?  I am not sure how to setup a measure for that in
Analysis Manager.

Email me personally.

Thanks!

Melissa Alamo

2. how to show foreign keys of a table

3. subtract dates

4. OPENROAD OPPORTUNITY

5. Subtract Dates and Loop

6. More SA Account?

7. Subtracting dates, evaluating it as a number

8. Access SQL Server 6.5 data from 7.0

9. subtracting date / time in SQL

10. Subtracting DATEs

11. Subtracting dates

12. FW: Subtracting dates

13. Subtracting Dates in Paradox 4.5 or 5.0