Problem with DateAdd Function in Query

Posted on 2011-10-13
Last Modified: 2012-08-14
Today is Oct. 13, 2011 (10/13/11) -

1. The following query will not return any records for 10/12/11 although records do exist:

     SELECT tblProd2.PRID, tblProd2.BsWtAvg, tblProd2.LoftAvg, tblProd2.ProdDate
     FROM tblProd2
     WHERE (tblProd2.ProdDate)=DateAdd("d",-1,Now());

The following queries will work:

     SELECT tblProd2.PRID, tblProd2.BsWtAvg, tblProd2.LoftAvg, tblProd2.ProdDate
     FROM tblProd2
     WHERE (tblProd2.ProdDate)<DateAdd("d",-1,Now());   'Returns all < 10/12/11

     SELECT tblProd2.PRID, tblProd2.BsWtAvg, tblProd2.LoftAvg, tblProd2.ProdDate
     FROM tblProd2
     WHERE (tblProd2.ProdDate)>DateAdd("d",-1,Now());  'Returns all > 10/12/11

     Attempts to use >= or <= in the criteria will not work.

I can use the following to return all records for 10/12/11:

     SELECT tblProd2.PRID, tblProd2.BsWtAvg, tblProd2.LoftAvg, tblProd2.ProdDate
     FROM tblProd2
     WHERE (tblProd2.ProdDate)>DateAdd("d",-2,Now()) AND (tblProd2.ProdDate) < Now());

Why doesn't     WHERE (tblProd2.ProdDate)=DateAdd("d",-1,Now());    return records for 10/12/11?
Question by:Tim313
    LVL 119

    Expert Comment

    by:Rey Obrero
    use Date() instead of Now()

     SELECT tblProd2.PRID, tblProd2.BsWtAvg, tblProd2.LoftAvg, tblProd2.ProdDate
         FROM tblProd2
         WHERE (tblProd2.ProdDate)=DateAdd("d",-1,Date());
    LVL 119

    Accepted Solution

    the Now() function includes Time values..
    LVL 30

    Expert Comment

    Why doesn't     WHERE (tblProd2.ProdDate)=DateAdd("d",-1,Now());    return records for 10/12/11?

    Using Date() Produces values like 10/12/11
    Using Now() Produces values like 10/12/11 09:10:00
    Which are not equal
    LVL 26

    Expert Comment

    Dates are the funnest thing there is!
    The previous posters have noted the beginning of the fun--that Date() and Now() do not return the same thing.
    One returns 13-Oct-2011 12:00:00 AM and the other returns 13-Oct-2011 02:37:00 PM

    That's the start
    If tblProd2.ProdDate is filled with values that were based on Now(), they will ALSO have times.
    So you can have stuff like Format(tblProd2.ProdDate, "dd-mmm-yyyy") that will return stuff showing today's date.
    But it may still fail tblProd2.ProdDate < #13-Oct-2012# because the time part is still there!

    Access ALWAYS stores a time.  If you don't specify one (you entered a date by hand -- 13-Oct-2011 or used Date()) then the time is midnight--12:00:00 AM
    If you use Now() or specify a time, then the specified time gets stored.

    Format() just changes what you SEE for string values, not the actual value of the datetime.
    Moreover, your regional settings influence how string-literal times are interpreted.
    01/06/11 can be the 6th of January or the 1st of June depending on the settings.

    Which is an EXTREMELY good reason NOT to use that format.
    ISO 8601 format -- 2011-10-13 -- never gets misinterpreted.
    Providing you are always using english, medium date never goes wrong either -- 13-Oct-11

    Danger, Will Robinson!  Never forget the time.  It's ALWAYS there

    This may play

         SELECT tblProd2.PRID, tblProd2.BsWtAvg, tblProd2.LoftAvg, tblProd2.ProdDate
         FROM tblProd2
         WHERE (tblProd2.ProdDate) >=CDate(Date()-1 & " 11:59:00 PM")
    LVL 48

    Expert Comment

    by:Gustav Brock
    It might be a more logical approach to use DateDiff:

    WHERE DateDiff("d", tblProd2.ProdDate, Date()) = 1

    However, should you have an index on ProdDate, this will not be used.


    Author Closing Comment

    Thanks to everyone. Since all replies were the same, capricorn1 gets the points for first response.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now