# Thread: Calculating Date Difference

1. Registered User
Join Date
Nov 2003
Location
england
Posts
95

## Unanswered: Calculating Date Difference

Hello,

I have a question, basically I want to find out the difference per in hours. How would i encase a function that just calculates the difference between the hours?

((SELECT
MAX(effective_date) -- (This is a date/time field)
FROM
[table name]
WHERE
[column_name] = [column_name] AND
[column_name] IN('r')) -
([logged_date] + [follow_up_hours])) -- another date/time field

Originally this would return me a bunch of numbers, would be really useful if i could just evaluate the hours!!!! I have tried multiplying the result by 24 but I dont seem to get anything useful back?!?!?!

Any Ideas?

Thanks

Lucy

2. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

3. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Your query currently returns a DATE, not a difference (NUMBER). If you subtract one date from another you get the difference as a number of days, and multipliying this by 24 will convert to hours.

4. Registered User
Join Date
Nov 2003
Location
england
Posts
95

## Date Diff

Thanks Tony, yeah the hours that came back didnt seem to make that much sense when I compared the data.

Sorry for not providing more info as its commercially sensitive data. I am just writing these in a SQL report writer on the software so not on the DB specificially. I thought if I encased the statement with an Extract I may have been able to get the select statement to just tell me the difference in hours. I am going to have another look at my hour statement to see where I mgith be going wrong......thanks for your advice....

Lucy

5. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
select (to_date('2010-02-19 23:00','YYYY-MM-DD HH24:MI') - to_date('2010-02-19 01:00','YYYY-MM-DD HH24:MI'))*24 HOURS FROM DUAL
SQL> /

HOURS
----------
22

6. Registered User
Join Date
Feb 2005
Posts
59
Originally Posted by lucyg_2000
([logged_date] + [follow_up_hours])) -- another date/time field
What does follow_up_hours hold? I'm guessing it should hold hours, i.e. fraction of a day, e.g. 1/24, 2/24 etc.

7. Registered User
Join Date
Nov 2003
Posts
2,993
Originally Posted by lucyg_2000
((SELECT
MAX(effective_date) -- (This is a date/time field)
FROM
[table name]
WHERE
[column_name] = [column_name] AND
[column_name] IN('r')) -
([logged_date] + [follow_up_hours]))
That's not a valid Oracle SQL statement.
Using [] to quote names is non-standard SQL and will not work with Oracle (or any other standard compliant database)

8. Registered User
Join Date
Nov 2003
Location
england
Posts
95

## Date Diff

Hello,

I know [] is used as an example so I havent been using it in the SQL. Yeah follow up hours contains hours. I need to specifically see if the complete date is > 2hrs from the target date, then I know it is late.

Lucy

9. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Lucy,

Do you still have a problem? If so it's not clear to me what it is.
I need to specifically see if the complete date is > 2hrs from the target date, then I know it is late.
Sounds like:
Code:
`case when (complete_date - target_date) > 2/24 then 'late' else 'on time' end`

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•