how to get Date in Access query

In Access 2000, which function could we use to get back the date value only?

CreateDate
20/4/2010 2:32:00 AM
20/4/2010 2:32:00 PM
19/4/2010 10:14:00 AM
17/5/2010 9:34:00 PM

If I query with:

 SELECT * FROM tblTest WHERE 开发者_JS百科CreateDate  <= #20/4/2010#

I just see the 19/4/2010 record only.

Actually, the result should display first 3 records, shouldn't it?

Your literal date value, #20/4/2010#, is actually a Date/Time value which represents midnight on Apr. 20. See this example from the Immediate Window which uses the Format() function to display the full Date/Time value represented by that literal.

? Format(#20/4/2010#, "d/m/yyyy hh:nn:ss AMPM")
20/4/2010 12:00:00 AM

So both 20/4/2010 2:32:00 AM and 20/4/2010 2:32:00 PM are excluded by your WHERE condition because they are greater than 20/4/2010 12:00:00 AM.

If you're running the query within an Access session, you can use the DateValue() function to convert your CreateDate values to midnight of the same date. This query should return what you expected:

SELECT * FROM tblTest WHERE DateValue(CreateDate) <= #20/4/2010#

Another approach is to use midnight one day later as your cut-off value.

SELECT * FROM tblTest WHERE CreateDate < #21/4/2010#

The second query could execute significantly faster because it wouldn't require applying the DateValue() function to every CreateDate value in tblTest. And if CreateDate is indexed, the second query would be faster still. The first query would not be able to take advantage of an index on CreateDate.

Also I prefer to use an unambiguous date format for literal values, so would write the query like this:

SELECT * FROM tblTest WHERE CreateDate < #2010/4/21#