[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