Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Location
    Locked in side a tiny room with a keyboard and many monitors glowing away in the dark...
    Posts
    25

    Unanswered: SQL 2005 Reports last 24 hours of data

    Hi Folks,

    I am working in Sql Server Reporting Services 2005 and need to create a report that only pulls the data from the last 24 hours. I have used the GETDATE() in the WHERE clause to no avail, such as:

    WHERE column_name BETWEEN GETDATE() AND GETDATE()-1

    Also have added GETDATE() to the SELECT statement with the same WHERE clause as above....

    So what am I missing....it needs to capture all data with in the 24 hour period from the time the report is run, so...if the report is run at midnight Monday it should pull all data back to midnight Sunday.

    Thanks in advance!!

    Torchwood
    Torchwood
    "understand, in a sick and twisted sort of way?"

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is a classic blunder, and one that you'll only make once

    the earlier date has to go first

    it's exactly the same as if you wanted to retrieve all the numbers between 10 and 5

    the BETWEEN syntax is evaluated as a pair of "less than or equal" conditions

    so
    Code:
    WHERE n BETWEEN 10 AND 5
    will be evaluated as
    Code:
    WHERE 10 <= n 
      AND       n <= 5
    which is clearly never true

    on the other hand, this --
    Code:
    WHERE n BETWEEN 5 AND 10
    will be evaluated as
    Code:
    WHERE 5 <= n 
      AND      n <= 10
    and clearly, the numbers 5, 6, 7, 8, 9, and 10 all satisfy those conditions

    it's the same with GETDATE() and GETDATE()-1

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

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Learned something new this morning: GetDate() - 1.
    I would have done it by using dateadd(dd, -1, GetDate()).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jul 2009
    Location
    Locked in side a tiny room with a keyboard and many monitors glowing away in the dark...
    Posts
    25
    Ok, just redid the code......If I say "I luv ya" would you be upset.....it figures it would be a situation of how things are ordered, me being dyslexic and all.

    And you are right, I won't forget this one!!

    THANK YOU, THANK YOU, THANK YOU!!!!
    Torchwood
    "understand, in a sick and twisted sort of way?"

Posting Permissions

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