Comparing Date and Date/Time values

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Hi,

I have an ADD_DATE_TIME field in my database which defaults to NOW() when a
row is inserted. I am providing my users a query with a DATE parameter in
order to identify all rows added on a specific date. How can I compare the
DATE (only) value the user enters against the values in the ADD_DATE_TIME
field and get all qualifying rows? Doing nothing, I only get rows where the
time value in the ADD_DATE_TIME is midnight. Attempts to format
ADD_DATE_TIME as a "Short Date" is also not working.

Thanks.
 
Chris said:
Hi,

I have an ADD_DATE_TIME field in my database which defaults to NOW() when a
row is inserted. I am providing my users a query with a DATE parameter in
order to identify all rows added on a specific date. How can I compare the
DATE (only) value the user enters against the values in the ADD_DATE_TIME
field and get all qualifying rows? Doing nothing, I only get rows where the
time value in the ADD_DATE_TIME is midnight. Attempts to format
ADD_DATE_TIME as a "Short Date" is also not working.
Click to expand...

Simple looking but less efficient...

WHERE DateValue([ADD_DATE_TIME]) = [Enter desired date]

uglier, but more efficient...

WHERE ADD_DATE_TIME BETWEEN DateAdd("d", -1, [Enter desired date]) AND
DateAdd("d", 1, [Enter desired date])

The reason is that anytime you look for a match based on an expression of
your data you lose any chance to use an index on the field being searched.
It's better to have a complicated expression to compare the raw field data
to and then the database engine can use an index.

BTW if the two parameter entries are made exactly identical as in my
example Access will still only prompt you once.
 
Rick,

Pardon me, but shouldn't that read

WHERE ADD_DATE_TIME BETWEEN [Enter desired date] AND
DateAdd("d", 1, [Enter desired date])

And to be totally accurate, you could use

WHERE ADD_DATE_TIME >= [Enter desired date] AND
ADD_DATE_TIME < DateAdd("d", 1, [Enter desired date])

Which would get every thing from Midnight on the date specified up to 11:59:59,
but not anything that occurred at exactly midnight on the following day.


Rick said:
Chris said:
Hi,

I have an ADD_DATE_TIME field in my database which defaults to NOW() when a
row is inserted. I am providing my users a query with a DATE parameter in
order to identify all rows added on a specific date. How can I compare the
DATE (only) value the user enters against the values in the ADD_DATE_TIME
field and get all qualifying rows? Doing nothing, I only get rows where the
time value in the ADD_DATE_TIME is midnight. Attempts to format
ADD_DATE_TIME as a "Short Date" is also not working.
Click to expand...

Simple looking but less efficient...

WHERE DateValue([ADD_DATE_TIME]) = [Enter desired date]

uglier, but more efficient...

WHERE ADD_DATE_TIME BETWEEN DateAdd("d", -1, [Enter desired date]) AND
DateAdd("d", 1, [Enter desired date])

The reason is that anytime you look for a match based on an expression of
your data you lose any chance to use an index on the field being searched.
It's better to have a complicated expression to compare the raw field data
to and then the database engine can use an index.

BTW if the two parameter entries are made exactly identical as in my
example Access will still only prompt you once.
Click to expand...
 
John Spencer (MVP) said:
Rick,

Pardon me, but shouldn't that read

WHERE ADD_DATE_TIME BETWEEN [Enter desired date] AND
DateAdd("d", 1, [Enter desired date])

And to be totally accurate, you could use

WHERE ADD_DATE_TIME >= [Enter desired date] AND
ADD_DATE_TIME < DateAdd("d", 1, [Enter desired date])
Click to expand...

You're correct of course. (long meeting today :)
 
Back
Top