[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Problem with DateAdd Function in Query

Posted on 2011-10-13
Medium Priority
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36963387
use Date() instead of Now()

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

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 36963391
the Now() function includes Time values..
LVL 31

Expert Comment

ID: 36963902
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 26

Expert Comment

ID: 36965246
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 52

Expert Comment

by:Gustav Brock
ID: 36969297
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

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

873 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