[AccessD] Tip concerning dates in Access

Hi Lambert

That's because Now adds a floor. Use a constant and you'll see the real difference:

n = Now : t = Timer : For i = 1 to 2 * 10 ^ 6 : d = CDate(Fix(n)) : Next : ? Timer - t
n = Now : t = Timer : For i = 1 to 2 * 10 ^ 6 : d = Fix(n) : Next : ? Timer - t

 0.712 
 0.391

This is VBA; in a query - where n is pulled from a table field - I doubt you can measure any difference.

/gustav


>>> Lambert.Heenan at AIG.com 24-10-2006 18:49:04 >>>
Hi Gustav,

I agree that the output of Fix(ADate) works too, but then I have to remember
to set the format property of a query field to "Short Date" (and we all know
how bad my memory is) or else the query results will just show the Long
value.

FYI there seems to be little to gained from eliminating the Cdate() call,
about 9.7 E-9 seconds per iteration.

CDate(Fix(Now())) took 1.851563 seconds for 2000000 iterations.
Fix(Now()) took 1.832031 seconds for 2000000 iterations.
DateValue(Now()) took 24.9375 seconds for 2000000 iterations.

Lambert :-)

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, October 24, 2006 11:58 AM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Tip concerning dates in Access


Hi Lambert

This can be even faster as CDate() is not needed. Fix() and Int() are
"transparent" as they often return the same data type as the input. Thus, to
kill the time portion of a Date/time value, just use:

A Date: Fix(dSomeDate)

As a bonus, removing CDate() makes the conversion run about 40% faster in
VBA and probably even faster in SQL as Fix() is native to JET SQL. Of
course, as you mention, this is only of importance in iterations and loops.

Also, study the thread 
  "Convert SQL Server Date/Time to Date" 
of late May this year and
  "Sorting old dates" 
of April this year on why Fix() and Abs() must be used to sort old Date/time
values.

/gustav


>>> Lambert.Heenan at AIG.com 24-10-2006 16:34:01 >>>
Well one reason is I never remember the DateValue function for some reason
:-), but a better reason is that DateValue is slower.

I'd guess the reason is that it takes a string as a parameter, so there's a
type conversion going on to pass in its data to work on, then it returns a
variant, which is another time wasting type conversion.  I just did a quick
test and found that on my box, 2,000,000 iterations of Cdate(Clng(Now()))
took 1.62 seconds, but 2,000,000 calls to DateValue(Now()) took 26.5
seconds. Not a huge difference I'll agree, but significant especially if you
are running the code against a table with potentially millions of rows. My
guess is that a Long is considerably Closer to a Date than a String or a
Variant is in terms of CPU cycles to do the conversion.

Lambert.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com 

Sent: Monday, October 23, 2006 5:26 PM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Tip concerning dates in Access


Why not just use DateValue?

Drew

-----Original Message-----
From: Heenan, Lambert [mailto:Lambert.Heenan at aig.com] 
Sent: Monday, October 23, 2006 11:06 AM
To: 'Access-D Email (accessd at databaseadvisors.com)'; 'ACCESS-L Email
(ACCESS-L at PEACH.EASE.LSOFT.COM)' 
Subject: [AccessD] Tip concerning dates in Access

Often you'll have a date field in a table that has the date and time stored
in it, but you may want to run a query that simply shows the date, without
the time.
 
Up to now I have been using Clng() to strip off the time part in a query
like this:
 
A Date: Cdate(Clng(dSomeDate))
 
but I just realized the lurking bug in this. CLng rounds up or down,
depending on which side of noon the time part is. So 1/1/2006 08:30:00 AM
would display as 1/1/2006, but 1/1/2006 12:30:00 PM would be 1/2/2006.
 
To avoid this problem use Fix() instead of Clng()...
 
A Date: Cdate(Fix(dSomeDate))
 
Fix always returns just the 'integer' part of the number passed to it. The
documentation suggests that Fix only returns an integer, but in fact it is
capable or returning Longs too, which is what date values are equivalent to.
 
HTH
 
Lambert