Format date/time column to select dates between

  • Thread starter Thread starter Brian L. Thor
  • Start date Start date
B

Brian L. Thor

I have a table with a Date/Time column (StartTime) that stamps transactions
with the current system date and time. I want to be able to query those
records by this column using a range of dates. For example: [StartTime]
Between 3/1/2004 and 3/31/2004. If I use this type of syntax Access does not
return any transactions from the last day of the date range. I can add 1 day
to the range effectively making my query [StartTime] Between 3/1/2004 and
4/1/2004. This seems to be effective but seems like a dumb way to handle the
situation. I've tried all sorts of Format and FormatDateTime combinations
without success. Does anyone know of a better way to handle this?

Thanks in advance, Brian.
 
Brian;

I tried using your syntax and could not see why it would not choose the last
date in the range. The only thing I could think of is to try putting a "#"
on either side of the dates......I've never had this trouble...
[StartTime]
Between #3/1/2004# and #3/31/2004#

That's all I can think of but I'm just a beginner at this....

Tony
 
That is exactly what should happen if the Dates in the table actually include
time values as well as dates. The records falling on the last date would only
be included if their time component was exactly midnight. Adding an extra day
is a completely legitimate way of handling this.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Lurch said:
Brian;

I tried using your syntax and could not see why it would not choose the last
date in the range. The only thing I could think of is to try putting a "#"
on either side of the dates......I've never had this trouble...
[StartTime]
Between #3/1/2004# and #3/31/2004#

That's all I can think of but I'm just a beginner at this....

Tony


Brian L. Thor said:
I have a table with a Date/Time column (StartTime) that stamps transactions
with the current system date and time. I want to be able to query those
records by this column using a range of dates. For example: [StartTime]
Between 3/1/2004 and 3/31/2004. If I use this type of syntax Access does not
return any transactions from the last day of the date range. I can add 1 day
to the range effectively making my query [StartTime] Between 3/1/2004 and
4/1/2004. This seems to be effective but seems like a dumb way to handle the
situation. I've tried all sorts of Format and FormatDateTime combinations
without success. Does anyone know of a better way to handle this?

Thanks in advance, Brian.
Click to expand...
Click to expand...
 
Solution One:

StartTime >= #3/1/2004# And StartTime < #3/31/2004#
Note the inclusion of the # as a date delimiter.

Solution Two:

DateValue(StartTime) Between #3/1/2004# and #3/31/2004#
 
Thanks to all who replied.

John, Solution One did not give me the correct result. However, Solution Two
seems to be perfect.

John Spencer (MVP) said:
Solution One:

StartTime >= #3/1/2004# And StartTime < #3/31/2004#
Note the inclusion of the # as a date delimiter.

Solution Two:

DateValue(StartTime) Between #3/1/2004# and #3/31/2004#



Brian L. Thor said:
I have a table with a Date/Time column (StartTime) that stamps transactions
with the current system date and time. I want to be able to query those
records by this column using a range of dates. For example: [StartTime]
Between 3/1/2004 and 3/31/2004. If I use this type of syntax Access does not
return any transactions from the last day of the date range. I can add 1 day
to the range effectively making my query [StartTime] Between 3/1/2004 and
4/1/2004. This seems to be effective but seems like a dumb way to handle the
situation. I've tried all sorts of Format and FormatDateTime combinations
without success. Does anyone know of a better way to handle this?

Thanks in advance, Brian.
Click to expand...
Click to expand...
 
Brian L. Thor said:
Thanks to all who replied.

John, Solution One did not give me the correct result. However, Solution Two
seems to be perfect.
Click to expand...

Just a comment though that whenever possible (especially on large tables)
you should avoid filtering on an expression of your field and should
instead try to use a syntax that filters directly against the field's
value. The reason is that only the latter can take advantage of indexing
on the field being filtered.

WHERE StartTime >= #3/1/2004# And StartTime < #4/1/2004#

Might not "feel" as correct because you are adding an extra day to the end
date to account for time values, but it will take advantage of an index on
the StartTime field whereas...

WHERE DateValue(StartTime) Between #3/1/2004# and #3/31/2004#

....will not.
 
Back
Top