date related question

[Carla]

Hello,
I am quite new in programming and really not comfortable
with date related issues. I need to send a yearly reminder
to our patients, my logic is

If ((Date()-365)=PatientLastServiceDate) pick this patient
and send a reminder.

I am assuming my application will run each day. My
question is: since I am using 365 as fixed number, does
anyone see any problem not picking some patients and/or
picking patients more than once, when we consider leap
year and other date related issues.
Thanks,
Carla.

 

[Allen Browne]

Instead of Date() - 365, use:
DateAdd("yyyy", -1, Date())

 

[Guest]

Thank you very much, can you give me a little bit more
information, why it is better this way?
Thanks.

 

[Carla]

I did a test on this and here is the result.

tdate = #2/28/2004#
myDate = tdate - 365 -> 2/28/2003
testdate = DateAdd("yyyy", -1, tdate) -> 2/28/2003

tdate = #2/29/2004#
myDate = tdate - 365 -> 3/1/2003
testdate = DateAdd("yyyy", -1, tdate) -> 2/28/2003

So if we use DateAdd("yyyy", -1, date()), we will be
picking the same patients at 2/28/2004 and 2/29/2004. If I
use 365 I am still ok, I am wondering if I may come across
any other problems.
Thanks,

 

[Doug Munich]

Considering leap years, holidays, and other date anomolies, you might want
to consider looking for

((Date()-365) <= PatientLastServiceDate

Of course that will pick up all the ones you've previously done too, so it
is probably a good idea to "flag" the Patients as you do them and then
exclude the flagged patients from the range above. By flag I mean you would
have a field like PatientNoticeSent that you set to Yes when you process
them, then set back to No when you update PatientLastService.

Doug

 

[Carla]

Doug, can you give me an example where equality may fail?
Thanks,

 

[Doug Munich]

Well if you miss running this process one day then the next day you would
have to look for (Date() - 365) and (Date - 366)? I guess if you run the
process every day there should be no issue with equality. I guess leap
years would only mean that the notice would go out one day early as in Feb
29, 2004 - 365 = Mar 1, 2003.

Doug