CODEWITHSUNDEEP
sqlms-access
Jesse
Jesse

Reputation: 408

Access query won't work when dates have times

I have a query stated:

select *
from tblClient
where IntakeDate = #5/31/2011#

I know for a fact there are 8 records that have that date. But none of records with that date are pulled by this query. Those 8 records have times as well as "short date" (e.g. "5/31/2011 1:42:00 PM")

As a test I set the date to exactly 5/31/2011 for one record, and the query will work for that one record. Clearly the time value is interfering with this query.

I do not want to change all the date data to a strict 'short date' format and would like to work with it as-is. Can anyone give me some idea how I can make this work?

Upvotes: 3

Views: 2677

Answers (2)

Mitch Wheat
Mitch Wheat

Reputation: 300499

Create a condition that encompasses a single day's time range:

select *
from tblClient
where IntakeDate >= #5/31/2011# AND < #6/1/2011#

[You could use the DateValue() function on your column, but that would prevent any index being used.]

Upvotes: 3

Devin Garner
Devin Garner

Reputation: 1406

The DateValue function truncates the time off the date

select *
from tblClient
where DateValue(IntakeDate) = #5/31/2011#

Upvotes: 2

Related Questions