Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2007
    Posts
    4

    Unanswered: SQL DATEDIFF in stored proc

    I have a weird problem in a stored procedure.

    Here's my table "Chemicals"

    ------------------------------------------------
    id formula EndDate
    3 a+b+c 31/12/2500 12:00:00 AM
    ------------------------------------------------

    and there is my SELECT command for my cursor

    -------------------------------------------------------------------
    SELECT id,formula FROM Chemicals WHERE id = @id AND DATEDIFF(day,EndDate,'2500-12-31') = 0
    -------------------------------------------------------------------

    the variable @id = 3.

    The problem is that it didn't return any row but if I write "DATEDIFF(day,EndDate,'2500-12-31') = 1" the row is returned.

    I used the same SELECT command for many tables but this one, DATEDIFF return 1 instead of 0. I tried to change the format of the date but nothing works...

    Anyone have a solution?

    Thank you.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    just curious, why bother with DATEDIFF if you're looking for a difference of 0 days?

    why not just

    ... AND EndDate = '2500-12-31'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2007
    Posts
    4
    Quote Originally Posted by r937
    just curious, why bother with DATEDIFF if you're looking for a difference of 0 days?

    why not just

    ... AND EndDate = '2500-12-31'
    indeed it works but I just find it weird because DATEDIFF should return 0 if both date are in the same interval.... but it didn't work for one of my stored proc...

    anyway..

    Thanks you!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is the EndDate column a string or a datetime datetype?
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Jul 2007
    Posts
    4
    the datatype is datetime

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I can't replicate your issue. This code works fine for me:
    Code:
    declare	@Chemicals table (id int, EndDate datetime)
    insert into @Chemicals(id, EndDate) values (3, convert(datetime, '31/12/2500 12:00:00 AM', 103))
    
    select	*
    from	@Chemicals
    WHERE id = 3 AND DATEDIFF(day,EndDate,'2500-12-31') = 0
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Jul 2007
    Posts
    4
    I know! that's why it's very weird!

    If I make a select command:

    SELECT DATEDIFF(day,EndDate,'2500-12-31') FROM Chemicals WHERE id = 3

    it return 0...

Posting Permissions

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