Results 1 to 9 of 9

Thread: Time & Dates!

  1. #1
    Join Date
    Feb 2004
    Posts
    41

    Unanswered: Time & Dates!

    Hey,
    im trying to figure out the number of hours between two dates with specified times e.g. From 14:00 on the 3rd March to 17:00 on the 14th March. This is my code and its returning 264.125 hrs when i think it should be giving me 267
    Expr1: ((([date_return]-[date_start])*24)+([time_return]-[time_start]))
    Can anyone help m e plz,
    Thanxs

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    DateDiff("h", startDate, endDate)
    Last edited by r123456; 02-14-04 at 09:54.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Posts
    41
    Im having problems getting that working - that keeps bringing up an answer of 264 hrs - Which is just calculating the total number of hours from the 3rd to the 14th - it doesnt seem to be taking the hours into account!

  4. #4
    Join Date
    Feb 2004
    Posts
    41
    Sorry its not that its not taking the hours into account, i ment it wasnt taking the time into account!

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    SELECT DateDiff("h",'2/13/2004 14:30', '2/14/2004 16:30' ) AS DIFF
    Last edited by r123456; 02-14-04 at 10:01.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Feb 2004
    Posts
    41
    but theres loads of records so i cant hard code the dates and times into it!!

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Code:
    columnA | columnB
    --------------------------
    2/13/04 2:30:00 PM  2/14/2004 4:30:00 PM
    
    Select DATEDIFF("h", columnA, columnB) from table;
    
    = 26
    Last edited by r123456; 02-14-04 at 10:35.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    r123456, if you look at the original post, you will see that the dates and times are kept in separate columns!!

    jill, any chance you can change your table layout? each of your date and time columns, if defined as datetime, is carrying both a date and time component

    so if you store "14:00 on the 3rd March" into two separate fields, you are most like storing

    2004-03-03 00:00:00
    1900-01-01 14:00:00 (or whatever the "zero date" value is)

    it is better to store both date and time into one field

    for one thing, it makes date calculations a lot easier, because then DATEDIFF would work the way r123456 has suggested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Posts
    41
    Thats perfect!! Thank you sooo much!!!!

Posting Permissions

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