V
Victoria@DIG
I couldn't quite tell what the difference is. Maybe that DATE() provides the
currnt calendar date only, where NOW() provides current date AND time?
currnt calendar date only, where NOW() provides current date AND time?
Victoria@DIG said:I couldn't quite tell what the difference is. Maybe that DATE() provides
the
currnt calendar date only, where NOW() provides current date AND time?
Albert D. Kallal said:That is absolutely correct, the now function has both a date and a time
portion.
it is for this reason that you want to exercise extreme caution with using
now() the as the default for fields. in fact I've seen some people making
huge mess this way, because if you use now as the default when you only need
the date your date field will actually have the following in it
1/27/2009 3:35:09 PM
What this means if you build a query to see give me all the invoices for
today's date, the query will not work because you must exactly match the
time also. in other words in my query I cannot go :
select * from tblInvoices where InvoiceDate = #1/27/2009#
The above will fail, and we see quite a few posts in the newsgroups asking
how come there date queries do not function correctly when they ask for a
particular date. The problem is is the above query will not work if you use
now) as the default In fact what you must use for the above query would be
select * from tblInvoices where InvoiceDate = #1/27/2009 3:35:09 PM#
You can see it is going be darn hard to match the date when you MUST include
the EXACT time to the second to get this query work correctly So if you have
an application in which you're going to query on that date, and you
accidentally
use the now() function, you've making things VERY difficult for a simple
date query...
In other words if you don't need the time portion in a date field, then you
really want to avoid it.
Chris O'C via AccessMonster.com said:Good point Albert.
If you've already got lots of records in the table inserted with the now()
function and want to query by date regardless of the time, you can use the
datevalue function.
select * from tblInvoices where datevalue(InvoiceDate) = #1/27/2009#
The solution or work around to continue to use high speed indexing
would be to use the following query
select * from tblInvoices where InvoiceDate = #1/27/2009# and
InvoiceDate < #1/28/2009#"
David W. Fenton said:Don't you mean:
select * from tblInvoices where InvoiceDate >= #1/27/2009# and
InvoiceDate < #1/28/2009#"
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.