Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23

    Unanswered: SQL Server DATEDIFF Behaving Badly

    Hi,

    I'm using the following SQL String in an ASP script with SQl Server:

    SELECT time, DATEDIFF(minute, time, '18:00:00') AS difference FROM appointments

    I have two appointments in the database - one with a time of 11:00 and one with a time of 14:00.

    When I run this SQL query, 'difference' comes out exactly 48 hours more than it should do. So for the first appointment, it says that the difference is 3300 minutes and for the second it is 3120 minutes.

    Any idea why this is happening? Is it something to do with the fact that I'm not using dates in the datetime format?

    Paul

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Could you give us some sample data and DDL ???
    What type is the time stored in ????
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I get 3 hours...

    Code:
    DECLARE @x varchar(5), @y varchar(5)
    SELECT @x = '11:00', @y = '14:00'
    SELECT DATEDIFF(mi, @x, @y)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't get anything remotely like 3 hours
    Code:
    create table timediffs
    ( id smallint not null identity primary key
    , appt  datetime
    )
    
    insert into timediffs ( appt ) values ( getdate() )
    insert into timediffs ( appt ) values ( '2005-05-10 11:00:00' )
    insert into timediffs ( appt ) values ( '2937-05-10 14:00:00' )
    
    select id
         , cast('18:00:00' as datetime) as starttime
         , appt
         , datediff(minute,appt,'18:00:00') as diff
      from timediffs
      
    1	1900-01-01 18:00:00.000	2004-05-10 12:39:57.927	-54885279
    2	1900-01-01 18:00:00.000	2005-05-10 11:00:00.000	-55410780
    3	1900-01-01 18:00:00.000	2937-05-10 14:00:00.000	-545595600
    oh my goodness, whatever could the reason be?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    oh my goodness, whatever could the reason be?
    Copious quantities of recreational pharmaceuticals???

    Whatever prompted you to pick such outrageous dates to go with what appear to be vaguely reasonable times ?!?! Did I somehow "miss a meeting" here?

    -PatP

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    they are appointments, with the exact times as specified

    they are not at all outrageous

    paul neglected to define the domain for his two appointments, so i chose two of my own personal appointments, one for a year from today, to come back here and see if i'm still as funny as i thought i was today, and the last one is the date i get out on parole for the serial killings of stupid IT managers i was convicted of here in ontario recently

    oops, did i just say that out loud?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've never understood the cereal killing bit.

    I always insist that my Wheaties are stone cold dead before they go into my bowl! I don't do the "garg" thing, although I'll happily support any of my Klingon friends who prefer their breakfast "live"!

    -PatP

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Quote Originally Posted by r937
    they are appointments, with the exact times as specified

    they are not at all outrageous

    paul neglected to define the domain for his two appointments, so i chose two of my own personal appointments, one for a year from today, to come back here and see if i'm still as funny as i thought i was today, and the last one is the date i get out on parole for the serial killings of stupid IT managers i was convicted of here in ontario recently

    oops, did i just say that out loud?
    Whoa !!! Thats a lot ...

    But remember ..
    "If you kill one person ... you go to jail ..
    If you commit mass murders ... you are given political asylum "

    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    maybe it's the weather, i swear, something just made me snippish today

    http://www.sitepoint.com/forums/showthread.php?t=168736
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    UK
    Posts
    23
    Solved it.

    The key was using VarChar for the time, instead of DateTime.

    Thanks Brett.


    Paul

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so now you have the appointment date in a datetime, and the time in a varchar?

    ewwww
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    paul neglected to define the domain for his two appointments, so i chose two of my own personal appointments, one for a year from today, to come back here and see if i'm still as funny as i thought i was today, and the last one is the date i get out on parole for the serial killings of stupid IT managers i was convicted of here in ontario recently
    I'm not sure how Canadian law works, but I know that in the states we can insist on "a jury of our peers". In your case, your "peers" are clearly the IT community as a whole, but more specifically the intelligent/articulate part of the community.

    Tried before a jury of your peers, you might well get a significant fine for littering (that sounds like a considerable mess, and the government doesn't budget for that type of clean up), but I can't imagine a reason for jail time. For that matter, if you get enough of the managers to effectively improve the gene pool, I'd expect that you'd get some kind of citation or award as a responsible citizen!

    -PatP

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The verdict is...

    ...not guilty, by reason of inanity.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    The verdict is...

    ...not guilty, by reason of inanity.
    As the kid would say "Well, duh!"

    -PatP

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by paulbrooks
    Solved it.

    The key was using VarChar for the time, instead of DateTime.

    Thanks Brett.


    Paul

    Don't mention it.

    Ain't this quite a little community we got here?

    I feel so at home with all the disconnected ramblings....

    Which could be done in the corral just as well....

    http://www.dbforums.com/showthread.p...20#post3675420
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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