date part

[Sam]

In a query, I need to select by just the date of a datetime field.
Date=#5/11/09# returns nothing because all the dates have time components.
How can I do this?

Thanks,
Sam

 

[Marshall Barton]

In a query, I need to select by just the date of a datetime field.
Date=#5/11/09# returns nothing because all the dates have time components.

Use the DateValue function.


WHERE DateValue(yourdatetimefield) = #5/11/09#

 

[Douglas J. Steele]

Use the DateValue function.


WHERE DateValue(yourdatetimefield) = #5/11/09#

Or (perhaps a little more efficient, since it avoids the function call)

WHERE yourdatetimefield BETWEEN #5/11/09# AND #5/12/09#

If you're using a parameter, that would be

WHERE yourdatetimefield BETWEEN [What Day?] AND DateAdd("d", 1, [What Day?])

 

[Marshall Barton]

"Marshall Barton"wrote

Use the DateValue function.

WHERE DateValue(yourdatetimefield) = #5/11/09#

Or (perhaps a little more efficient, since it avoids the function call)

WHERE yourdatetimefield BETWEEN #5/11/09# AND #5/12/09#

If you're using a parameter, that would be

WHERE yourdatetimefield BETWEEN [What Day?] AND DateAdd("d", 1, [What Day?])

Good idea Doug, but to be very picky, I think it should be

.... BETWEEN #5/11/09# AND #5/11/09 23:59:59#
or
.... BETWEEN [What Day?] AND DateAdd("s", 86399, [What Day?])
;-)

 

[Sam]

really helpful.
Thanks so much.

"Marshall Barton"wrote

Sam wrote:
In a query, I need to select by just the date of a datetime field.
Date=#5/11/09# returns nothing because all the dates have time components.


Use the DateValue function.

WHERE DateValue(yourdatetimefield) = #5/11/09#

Or (perhaps a little more efficient, since it avoids the function call)

WHERE yourdatetimefield BETWEEN #5/11/09# AND #5/12/09#

If you're using a parameter, that would be

WHERE yourdatetimefield BETWEEN [What Day?] AND DateAdd("d", 1, [What Day?])

Good idea Doug, but to be very picky, I think it should be

.... BETWEEN #5/11/09# AND #5/11/09 23:59:59#
or
.... BETWEEN [What Day?] AND DateAdd("s", 86399, [What Day?])
;-)

 

[John W. Vinson]

Good idea Doug, but to be very picky, I think it should be

... BETWEEN #5/11/09# AND #5/11/09 23:59:59#
or
... BETWEEN [What Day?] AND DateAdd("s", 86399, [What Day?])
;-)

Or even pickier...

= [What Day?] AND < DateAdd("d", 1, [What Day?])