date part

  • Thread starter Thread starter Sam
  • Start date Start date
S

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
 
Sam said:
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.
Click to expand...


Use the DateValue function.


WHERE DateValue(yourdatetimefield) = #5/11/09#
 
Marshall Barton said:
Use the DateValue function.


WHERE DateValue(yourdatetimefield) = #5/11/09#
Click to expand...

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?])
 
Douglas said:
"Marshall Barton"wrote
Use the DateValue function.

WHERE DateValue(yourdatetimefield) = #5/11/09#
Click to expand...


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?])
Click to expand...


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?])
;-)
 
really helpful.
Thanks so much.

Marshall Barton said:
Douglas said:
"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#
Click to expand...


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?])
Click to expand...


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?])
;-)
Click to expand...
 
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?])
;-)
Click to expand...

Or even pickier...
= [What Day?] AND < DateAdd("d", 1, [What Day?])
Click to expand...
 
Back
Top