Results 1 to 9 of 9
  1. #1
    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. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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. #4
    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. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Feb 2005
    Posts
    57
    Quote Originally Posted by lucyg_2000 View Post
    ([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. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by lucyg_2000 View Post
    ((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. #8
    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. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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
  •