MSAccess: Return query for specific date from datetime value

1
votes

I am trying to run a query from a linked table in MS SQL Server that has a datetime field. I'm trying to run a simple query to search for records for a specific date (#03/24/2018#), but since the datetime field has the time as well, I am not getting any records unless I specify the time range using BETWEEN with the time (Between #03/24/2018 00:00:00 AM# And #03/24/2018 11:59:59 PM#).

Original query which does not return desired output:

SELECT *
WHERE MyDateTimeField) = #3/24/2018#;

Query

SELECT *
WHERE MyDateTimeField) Between #3/24/2018 00:00:00 AM# And #3/24/2018 23:59:59#);

Is there a workaround to this as to not have to use a BETWEEN operator with the time?

1
ms-accessms-access-2016

1 Answers

0
votes

To avoid time portion, check forMyDateTimeFieldequal/greater than searched day and less than next day:

SELECT *
FROM MyTable
WHERE 
    MyDateTimeField >= #3/24/2018# 
  AND 
    MyDateTimeField < DateAdd("d",1,#3/24/2018#);

In opposite of convertingMyDateTimeFieldto date, this does not prevent index usage and handlesNullValues onMyDateTimeField.