471,888 Members | 2,188 Online

# DateDiff function question

Hello all. I have a really basic question that I hope someone has a
better answer for. I apologize in advance-- I know this is probably a
really basic question but I am used to Oracle rathern than Access.

I have a database where they customer wants to purge records from
certain tables after three years. When I was asked to make the changes
I originally thought this was an incredibly simple thing to do. I
looked at the function list and assumed that a one line query using

Unfortunately, the DateDiff function with the "yyyy" does not actually
calculate the number of years difference between two dates; it returns
the number of times the year portion of the date changes. For example,

DateDiff("yyyy",#31/12/2004#, #24/06/2005) returns 1
DateDiff("yyyy",#01/01/2005#, #24/06/2005) returns 0

even though the time difference is not a full year between the two
dates.

I know I can do days instead but then I have to add the calculations
for leap years, etc. and it just doesn't seem like a nice clean and
elegant way to do it. Is there a function or something that I am
missing?

Thanks.
Bill

Nov 13 '05 #1
5 6547
Let's try a different function instead.

Example:

You could also use DateSerial which splits the year, month, and date into
separate items.

Example:
DateSerial(Year(Date()) - 3, Month(Date()), Day(Date()))

You would then use the result of either of these in the criteria.

Example Criteria for the date field:

This will filter the query to only return records whose date is 3 years ago
or older.

--
Wayne Morgan
MS Access MVP
<mc******@yahoo.com> wrote in message
Hello all. I have a really basic question that I hope someone has a
better answer for. I apologize in advance-- I know this is probably a
really basic question but I am used to Oracle rathern than Access.

I have a database where they customer wants to purge records from
certain tables after three years. When I was asked to make the changes
I originally thought this was an incredibly simple thing to do. I
looked at the function list and assumed that a one line query using

Unfortunately, the DateDiff function with the "yyyy" does not actually
calculate the number of years difference between two dates; it returns
the number of times the year portion of the date changes. For example,

DateDiff("yyyy",#31/12/2004#, #24/06/2005) returns 1
DateDiff("yyyy",#01/01/2005#, #24/06/2005) returns 0

even though the time difference is not a full year between the two
dates.

I know I can do days instead but then I have to add the calculations
for leap years, etc. and it just doesn't seem like a nice clean and
elegant way to do it. Is there a function or something that I am
missing?

Thanks.
Bill

Nov 13 '05 #2
Doh!
Boy do I feel stupid. :-)

Thanks. That works great.

Bill

Nov 13 '05 #3
mc******@yahoo.com wrote:
Hello all. I have a really basic question that I hope someone has a
better answer for. I apologize in advance-- I know this is probably a
really basic question but I am used to Oracle rathern than Access.

I have a database where they customer wants to purge records from
certain tables after three years. When I was asked to make the changes
I originally thought this was an incredibly simple thing to do. I
looked at the function list and assumed that a one line query using

Unfortunately, the DateDiff function with the "yyyy" does not actually
calculate the number of years difference between two dates; it returns
the number of times the year portion of the date changes. For example,

DateDiff("yyyy",#31/12/2004#, #24/06/2005) returns 1
DateDiff("yyyy",#01/01/2005#, #24/06/2005) returns 0

even though the time difference is not a full year between the two
dates.

I know I can do days instead but then I have to add the calculations
for leap years, etc. and it just doesn't seem like a nice clean and
elegant way to do it. Is there a function or something that I am
missing?

Thanks.
Bill

This seems related to determining how old someone is given their
birthdate.

If you don't mind using the Format() function in a SQL string,

SELECT Int(Format([CDate],"yyyy.mmdd") - Format([BDate],"yyyy.mmdd"))
AS Age FROM tblBirthdays;

Originally I used Val() around the Format but I don't think I need it.
If you don't mind assuming True = -1,

SELECT Year([CDate]) - Year([BDate]) + IIf(Month([BDate]) =
Month([CDate]), Day([BDate]) > Day([CDate]), Month([BDate]) >
Month([CDate])) AS Age FROM tblBirthdays;

else

SELECT Year([CDate]) - Year([BDate]) + IIf(Month([BDate]) =
Month([CDate]), IIf(Day([BDate]) > Day([CDate]), -1, 0),
IIf(Month([BDate]) > Month([CDate]), -1, 0)) AS Age FROM tblBirthdays;

should be quite robust.

Note that the solution on Dev's site,

SELECT DateDiff("yyyy",[Bdate],[CDate]) + Int(Format([CDate],"mmdd") <
Format([Bdate],"mmdd")) AS Age FROM tblBirthdays;

only requires "mdd" rather than "mmdd" in both spots. BDate in
tblBirthdays stands for BirthDate and CDate stands for CurrentDate. I
envisioned something like Date() to be used in place of [CDate]. Be
sure to test any of these methods before use including tests like
3/1/03 to 2/29/04 that mess up simpler solutions that use the day of
the year. Perhaps some unforseen condition that messes up my solution
caused someone to split the calculation into two parts.

James A. Fortune

Nov 13 '05 #4
<ji********@compumarc.com> wrote in message
mc******@yahoo.com wrote:
Note that the solution on Dev's site,

SELECT DateDiff("yyyy",[Bdate],[CDate]) + Int(Format([CDate],"mmdd") <
Format([Bdate],"mmdd")) AS Age FROM tblBirthdays;

only requires "mdd" rather than "mmdd" in both spots.

Not so. You need the preceding 0 so that, for instance, February birthdays
are known to occur in October.

Format(#2/14/1990#, "mdd") will yield 214, while Format(#10/1/2005#, "mdd")
will yield 1001. Since we're comparing text values here, 214 is NOT less
than 1001. 0214 is less than 1001 though.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

Nov 13 '05 #5

Douglas J. Steele wrote:
Format(#2/14/1990#, "mdd") will yield 214, while Format(#10/1/2005#, "mdd")
will yield 1001. Since we're comparing text values here, 214 is NOT less
than 1001. 0214 is less than 1001 though.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

You're not comparing text values there. Try it out. SQL reads 0214 as
214. Maybe you're thinking of Format\$.

James A. Fortune

Nov 13 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.