Date comparisons

[swas]

Hi,

Regarding date comparisons, all of the following will return the same result:

If Date1 > Date2 Then ...

If Datediff("d", Date1, Date2) < 0 Then ...

If Date2 - Date1 < 0 Then ...

But which is the more correct? I am in Australia so my system date is
dd-mm-yyyy, and it is very easy to get mixed up when using sql etc... where
US format is needed (mm-dd-yyyy). I'm trying to tidy my date coding habits up
a little, and assume using Datediff() should be internationally compliant.

The same for adding dates:

Date1 + 7 adds 7 days to the date, but

DateAdd("d", 7, Date1) should be the correct format to use?

Any rules of thumb appreciated.

Thanks.


swas

 

[Rick Brandt]

Hi,

Regarding date comparisons, all of the following will return the same result:

If Date1 > Date2 Then ...

If Datediff("d", Date1, Date2) < 0 Then ...

If Date2 - Date1 < 0 Then ...

But which is the more correct? I am in Australia so my system date is
dd-mm-yyyy, and it is very easy to get mixed up when using sql etc... where
US format is needed (mm-dd-yyyy). I'm trying to tidy my date coding habits up
a little, and assume using Datediff() should be internationally compliant.

The same for adding dates:

Date1 + 7 adds 7 days to the date, but

DateAdd("d", 7, Date1) should be the correct format to use?

Any rules of thumb appreciated.

Two things. If you were using any of those comparisons in a query then the
first is the best. You always want to compare directly to a field rather than
the result of an expression since comparing to a field can make use of an index.
Comparing to an expression cannot and thus the whole table must be scanned.
Using an expression on the other side of the comparison operator is okay as long
as that expression doesn't include a field (as that would also force a full
table scan).

In code though where these really are single values then anything you've got
there will work and any speed difference would be negligible. However; the ones
using arithmetic operations directly on the date values could be argued against
on the grounds that they are platform dependant. They work only because
Access/Jet stores dates under the covers as double numeric values with the
"ones" place representing days. If you ever had to port your code (or query) to
a platform where that wasn't the case then those methods would fail.

Of course there's no guarantee that the hypothetical new platform would support
DateDiff or DateAdd either, but they are at least methods specifically designed
to perform "date math" whereas the direct arithmetic on date values is sort of a
hack (IMO).

If nothing else, getting into the habit of always using the date-math functions
would serve you better should you later need to work in an environment where
dates are not stored as numbers or as different kinds of numbers such that the
same arithmetic doesn't produce results identical to Access.

This is definitely a question of "theory versus practice" and I for one have
lots of places where I have simply used the direct date math methods so I don't
want to give the impression that those are "wrong". Only that from a "best
practice" viewpoint using the specific date functions has some advantages, even
if only that they self-document what you are doing a bit better.

 

[Allen Browne]

DateDiff() is the safest choice for 3 reasons:
a) It will perform a date comparison (i.e. it won't confuse the data types.)
b) It operates on the date-only part of the values.
c) It handles Nulls correctly.

Issue (a)
======
By way of example, create a form named Form8, with 2 unbound text boxes
named Text0 and Text2. Enter the values 1/1/2007 and 31/12/2007 in the 2
boxes (in keeping with your Australian date format.)

Now press Ctrl+G to open the Immediate Window, and enter:
? Forms!Form8!Text2 - Forms!Form8!Text0
This yields "Error 13 - Type Mismatch", because Access does not understand
the data type.

Now try:
? DateDiff("d",Forms!Form8!Text0, Forms!Form8!Text2)
This evaluates okay: Access forces the data types to Date/Time.

Issue (b)
======
The other problem is if the dates contain a time value. Still in the
Immediate window, try:
? DateDiff("d", #1/1/2007 23:00:00#, #2-Jan-2007#)
It reports that the difference is 1 day. Now try:
? #2-Jan-2007# - #1/1/2007 21:00:00#
This results in 0.125 (i.e. one-eighth of a day.) If you assign the result
to a whole number field, you would therefore get zero as the result, where
the DateDiff() yielded one.

Issue (c)
======
Are these 2 ways of coding the same:
Version 1:
If Me.Date1 > Me.Date2 Then
MsgBox "Date1 is more recent"
Else
MsgBox "Date1 is not more recent"
End If
Version 2:
If Me.Date2 <= Me.Date1 Then
MsgBox "Date1 is more recent"
Else
MsgBox "Date1 is not more recent"
End If

If you think they will yield the same result, try it. Leave one box blank
(no entry), and see if the messages contradict each other. Explanation in
Error 6 on this page:
http://allenbrowne.com/casu-12.html

Finally, I've been using Access in Australia for 15 years, and here's what
I've learned about getting it to understand our d/m/y dates correctly:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[swas]

Thanks both Rick and Allen.

I was pretty much on the way as both suggested, mainly because I'm in the
middle of a bunch of date relevant processing, hence my concerns. I was
worrying about a heap of other older code where I haven't been so thoughtful.

Allen - I should have checked your pages first!

Rick - I suppose a couple of new functions to replace DateAdd or DateDiff
would be easier to implement on a new platform rather than analysing every
situation where dates are used in logic or calcs.

Just one further question on the same line, in keeping with international
date correctness. Based on Au dd-mm-yyyy system date, if I have a
DAO.Recordset open, and want to add / change a date field, I need to use:

rs!DateFieldName = Format(Date1, "dd-mm-yyyy")

If I don't use this, a date of Au 2-7-2007 gets updated as 7-2-2007 if I use
## delimiters only. In otherwords, the format is forced to Au format.

But if the system date is changed to something else, this code will of
course be incorrect. What is the correct method?

Great info and responses - thanks very much.


swas

 

[Rick Brandt]

Thanks both Rick and Allen.

I was pretty much on the way as both suggested, mainly because I'm in the
middle of a bunch of date relevant processing, hence my concerns. I was
worrying about a heap of other older code where I haven't been so thoughtful.

Allen - I should have checked your pages first!

Rick - I suppose a couple of new functions to replace DateAdd or DateDiff
would be easier to implement on a new platform rather than analysing every
situation where dates are used in logic or calcs.

Just one further question on the same line, in keeping with international
date correctness. Based on Au dd-mm-yyyy system date, if I have a
DAO.Recordset open, and want to add / change a date field, I need to use:

rs!DateFieldName = Format(Date1, "dd-mm-yyyy")

If I don't use this, a date of Au 2-7-2007 gets updated as 7-2-2007 if I use
## delimiters only. In otherwords, the format is forced to Au format.

But if the system date is changed to something else, this code will of
course be incorrect. What is the correct method?

Great info and responses - thanks very much.

Date literals should always be a non-ambiguous format or the US format. I
recommend always using the ISO format yyyy-mm-dd. That is recognized as a valid
format in the most places regardless of local Windows settings.

 

[Allen Browne]

No. Don't use Format(). Format() outputs text. Therefore you are converting
the date value to a text string. Then you are assigning the text string to a
date field, so Access converts the string back into a date value. That's not
good.

Internally, Access stores date/time values as a real number, where the
integer part represents the date, and the fraction part represents the
fraction of a day (e.g. noon = 0.5, 6am = 0.25.) The display format is only
applied at display time. Therefore, you are better off to assign a Date/Time
value directly to a date/time field. So, assuming Date1 is a Date type
variable, you are better to code just:
rs!DateFieldName = Date1

And that works regardless of the user's date settings.

Read the article I suggested on how to avoid the 3 cases where Access can
get your dates wrong.