Date() versus Now()

[Mark]

Hi,

What is the differences in using Date() and Now()? I have
hasd trouble with using Date(), so I replaced it with Now
(), but I am now having trouble running a query with
criteria of >=Now(). It returns all records greater than
today's date and ignores records that are equal to todays
date.

Thanks!

 

[James Goodman]

Now() returns the date & time, Date() returns the date only.

So using WHERE >=Now() will only return records after the current date &
time

 

[Albert D. Kallal]

You want to use very much caution here. Now() and date() are both there for
a reason, and they are VERY different commands.

If you start using Now() for a default in a field, that field is storing
both the date, and ALSO THE TIME!. That means you will have to use both the
date AND TIME when looking for date matches. Supplying just the date will
not work..you will also have to supply the EXACT time you used the now! This
is likely NOT WHAT YOU want. You can really mess up a database when you do
this.

If you did put in the time portion, then you need to use a date range to get
all of the dates for a given day...since testing JUST for a date will not
work!

select * from tblCustomer where InvoiceDate >= #02/25/2004# and <
"02/26/2004#

Note how I used a range to get all the dates and "time" for the 25th of Feb.
So, be careful. Now() and date() are there for a reason, and THEY ARE NOT
interchangeable.

If you have messed up your data, and want to remove the time portion, then
you will need to run a update query. Something like:

update tblCustomer set InvoiceDate = datevalue([InvoiceDate])

As always, make a backup before you start using dangerous updates like the
above.