Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    2

    Cool Unanswered: getdate() not returning a value

    I have a strange problem occurring when I am using getdate() in a stored proc. I want to get some data from a table within a date range using getdate.
    I have a begin and end dates on a table and want to retrieve a guid and some other information based on the current date. So, wherever today's date falls between the begin date and the end date, I want the information from that row.

    For example,

    select * from polldates
    where (pollbegindate >= getdate() and pollenddate <= getdate())


    This works fine Monday through Saturday. I get a value returned from getdate() correctly and am able to retrieve the information that I need. However, on Sunday, getdate returns nothing when I run the stored procedure. Any clues? Am I just crazy or has anyone else seen this type of thing happen?

    Any help would be greatly appreciated!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I doubt very much that GetDate() isn't returning a value. Your query may not be returning rows, but I'm very sure that GetDate() is returning a value.

    -PatP

  3. #3
    Join Date
    Sep 2004
    Posts
    2
    If you are sure that get date is returning a correct value but I am not getting anything back from my query can you suggest how to improve the query?

    For example, the begin date is 9/5/04 and the end date is 9/11/04.

    Thanks!

  4. #4
    Join Date
    Sep 2003
    Posts
    364
    Is it safe to assume pollbegindate and pollenddate are datetime datatypes in the table? Please post the enitre proc. There may be another problem.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    I doubt very much that GetDate() isn't returning a value. Your query may not be returning rows, but I'm very sure that GetDate() is returning a value.

    -PatP

    Well that was CERTAINLY helpful...

    Dude

    Do SELECT GetDate()...what do you see?

    Ahh microseconds....

    USE DATEDIFF

    But the logic doesn't make sense...

    You want all begin dates that are today and greater but all end dates that are less that or equal today...which means...

    And day where the start and end are equal and it's TODAY

    Johhny...tell him what he's won......
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Maybe we all need to read. Now I feel like an idiot (well, I almost always feel like an idiot, but that's another matter).
    Code:
    SELECT *
       FROM polldates
       WHERE  pollbegindate <= getdate()
          AND pollenddate >= getdate()
    The previous code was looking for rows where the begindate was greater than the enddate!

    -PatP

  7. #7
    Join Date
    Sep 2003
    Posts
    364
    Even with the screwed up logic why does it return records everyday but Sunday?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Me no know.

    Without seeing the real query and the underlying data, I can offer a gazillion guesses, but no hard facts.

    -PatP

Posting Permissions

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