Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: Query Based On System Date

    Can someone please help me with the syntax of my query below. I am trying to create a query that queries a table for records entered on the previous day. Am I approaching this the right way?


    Select a, b, c, d, [Completed Date]
    From mytable
    where ([Completed Date] = GETDATE() - 1)

    There are records in mytable with yesterdays date but I am not getting any results. I think it has to do with a mismatch in my date format? The format of the dates in my completed_date field is (1/21/2009 7:52:38 AM). I don't care about the time so I'm looking to change the format of the [Completed Date] field in my database to MMDDYYYY as well as the parameter that I am using in my where clause.

    I tried the code below as well and still can't get it to work.

    Select a,b,c,d, (SELECT REPLACE(CONVERT (VARCHAR(10),[Completed Date], 101), /, ) AS MMDDYYYY from mytable)
    from mytable
    where ([Completed Date] = GETDATE() - 1)


    Thanks,

    DB

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rows for the previous day can be obtained by using an expression for today's date which has no time component, i.e. midnight

    this is accomplished by stripping off the time portion from GETDATE()

    there are many ways to do this, but the most efficient is DATEADD(day,DATEDIFF(day,0,GETDATE()),0)

    this expression calculates the number of day boundaries between GETDATE() -- regardless of the time -- and a "zero" or base date

    then that number is added to the base date

    to get yesterday, simply add back one day less

    so your WHERE clause for yesterday's rows would be:
    Code:
     WHERE [Completed Date] >= DATEADD(day,DATEDIFF(day,0,GETDATE())-1,0)
       AND [Completed Date]  < DATEADD(day,DATEDIFF(day,0,GETDATE()),0)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2005
    Posts
    91
    Thanks R37! I'll run a few tests tonight.

  4. #4
    Join Date
    Nov 2005
    Posts
    91
    Thanks R37! What you provided me with is exactly what I was looking for.

Posting Permissions

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