[AccessD] Tip concerning dates in Access

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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com