CODEWITHSUNDEEP
sqldatetimems-accessselect
Gareth
Gareth

Reputation: 5243

Selecting Yesterdays Records using DateTime Field

I'm trying to select from a table all of yesterdays records (not the last 24 hours worth of records). The field in question is defined as DateTime (in Access 2007) and is formatted dd/mm/yyyy hh:mm:ss.

My first thoughts (as per many other Q&A's) would be to take an approach like:

SELECT * FROM someTable WHERE DateAdded = DateAdd("d", -1, Now())

As mentioned though, the fields format means that no results are returned as the condition compares the time value as well.

My next thought was to convert the DateTime fields into dates like so:

SELECT * FROM someTable WHERE cDate(Added) = DateAdd("d", -1, cDate(Now()))

Once again, no records are returned though.

If I run the following queries though, both return 29/10/2014:

--SELECT yesterdays date
SELECT DateAdd("d", -1, cDate(Now())) --Returns 29/10/2014

--SELECT the date of the last record
SELECT TOP 1 cDate(Added) FROM someTable ORDER BY Added DESC --Returns 29/10/2014

So my question is how do you select yesterdays records?

Upvotes: 2

Views: 1940

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123484

Your WHERE clause needs to include all Date/Time values from yesterday (i.e. the 24-hour period between yesterday at midnight and today at midnight), so you need to do

SELECT * 
FROM someTable
WHERE DateAdded >= DateAdd("d", -1, Date())
    AND DateAdded < Date()

Upvotes: 2

Related Questions