comparing two dates. Equal isn't working as well

Question

I've got trouble with comparing two dates: There is short datetime field in database, recorded by Microsoft Access 2002-2003 data extension (#yyyy/mm/dd#). When I tried to compare via operators "greater than" and "less than" it works fine, but when I replaced with the operator "equal" it's giving me an empty result. Here are the queries:

SELECT * FROM Logs WHERE (Date = #2015.06.11#) - returns an empty result, but

SELECT * FROM Logs WHERE (Date > #2015.06.10#) - is okay. What did I do wrong?

Answer 1

The Date/Time value #2015.06.11# includes a time component, which is 12:00 AM.

If any of your stored values for that date include a time component other than 12:00 AM, they will be excluded by your WHERE clause.

Use a modified WHERE clause to retrieve all the rows for your target date (regardless of time of day) and also display the time components of your stored date values:

SELECT
    l.*, 
    Format(l.Date, 'h:nn ampm') AS time_component
FROM Logs AS l
WHERE l.Date >= #2015.06.11# AND l.Date < #2015.06.12#;

Note, for my system locale, #2015.06.11# is not recognized as a Date/Time value because of the dot separators. However, I presume that format is valid for your locale. If there is any doubt, try with a different separator: #2015-06-11# or #2015/06/11#

Answer 2

I don't think you have an issue. Your table just doesn't contain an entry with that date.