Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    43

    Unanswered: Return records for yesterday

    Hi,

    I have a simple query that needs to be run each day for event that happened yesterday.

    I was using the following but if I use a date range instead I get more results so mine is faulty, Ive tried CURDATE(), CURRENTDATE, etc nothing seems to get me the actual results. Any help is appreciated.. please see syntax problem below

    where a.adm_ts >= DateAdd(Day, DateDiff(Day, 0, GetDate()),0)

    I want all records from 00:00 to 23:59 for yesterday ..

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This should help you on your way
    Code:
    SELECT GetDate() As [right_now]
         , DateAdd(dd, DateDiff(dd, 0, GetDate()), 0) As [earlier]
         , DateAdd(dd, DateDiff(dd, 0, GetDate()) + 1, 0) As [tomorrow]
         , DateAdd(dd, DateDiff(dd, 0, GetDate()) - 1, 0) As [yesterday]
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    for this use index
    select * from urtable where a.adm_ts >=dateadd(dd,datediff(dd,0,getdate()),0) and a.adm_ts <dateadd(dd,datediff(dd,0,getdate()),1)

Posting Permissions

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