What is the difference between the functions DATE() and NOW()?

  • Thread starter Thread starter Victoria@DIG
  • Start date Start date
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?
 
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?
Click to expand...

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.
 
Thank you all.

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.
Click to expand...
 
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#
Click to expand...

The above will work. However, keep in mind with the above access can't use
an index on the above since the table column is wrapped in an expression.
So, if the file is going to have a large number of records in it, you can
NOT use indexing and it will run slow. And, if you're multiuser on a
network, then the whole table will be dragged across the network.

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#"
 
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#"
Click to expand...

Don't you mean:

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#"
Click to expand...

Yes...thanks for that.....
 
Back
Top