Could This Be a Leap Year Problem?

[Steve Andrews]

Hi All,

Upon just uprading to V10.5 (from V5), a user notes that several records are missing from a report. I thought at first that this was simply one of the many ways that stricter syntax was required for later versions. But upon investigating, I found something which I cannot explain. It seemingly has to do with the between() function and/or the involvement of a leap year.

There were four missing records for the report in question. From the code below, you will see that if I look for records with the Serv_start date between 1/1/2011 and 2/20/2013, I find four matching records. As shown, all four of these have Serv_start dates of 2/21/2012.

But if I look for records with Serv_start dates between 1/1/2012 and 2/20/2013, it finds no records!!!

fltr = "medicaid='02 10 097352-01'.AND.between(serv_start,{01/01/2011},{02/20/2013})"

?fltr
= "medicaid='02 10 097352-01'.AND.between(serv_start,{01/01/2011},{02/20/2013})"

?table.external_record_content_get("caregvn","dtoc(serv_start)","",fltr)
= 02/21/2012
02/21/2012
02/21/2012
02/21/2012

?a5_get_records_in_query("caregvn",fltr)
= 4




fltr = "medicaid='02 10 097352-01'.AND.between(serv_start,{01/01/2012},{02/20/2013})"

?fltr
= "medicaid='02 10 097352-01'.AND.between(serv_start,{01/01/2012},{02/20/2013})"

?table.external_record_content_get("caregvn","dtoc(serv_start)","",fltr)
= ""

?a5_get_records_in_query("caregvn",fltr)
= 0

I've heard of problems encountered on previous leap years but have found no conclusive postings thus far. Can any explain what I've encountered? Thanks.

Steve

[G Gabriel]

Re: Could This Be a Leap Year Problem?

Seems to work OK in v8.
Have your tried between_date() instead?

[Steve Andrews]

Re: Could This Be a Leap Year Problem?

Between_Date() doesn't return a logical value. Instead, it returns a character string of code (which encompasses the Between() function).

I'm still perplexed on why date comparisons behave so incorrectly. I've even changed the Between() expression to one of the format: "Serv_st>={01/01/2012}.AND.Serv_start<={02/20/2013}". The problem still persists.

[Stan Mathews]

Re: Could This Be a Leap Year Problem?

If I understand the issue, always in doubt, I don't see the problem testing solely on dates.

fltr = "between(dt,{01/01/2012},{02/20/2013})"

? table.external_record_content_get("anames","dtoc(dt)","",fltr)
= 01/05/2012
02/21/2012
02/21/2012
02/21/2012
03/07/2012


fltr = "between(dt,{01/01/2011},{02/20/2013})"

? table.external_record_content_get("anames","dtoc(dt)","",fltr)
= 01/05/2012
02/21/2012
02/21/2012
02/21/2012
03/07/2012


fltr = "between(dt,{02/01/2011},{02/29/2012})"
? table.external_record_content_get("anames","dtoc(dt)","",fltr)
= 01/05/2012
02/21/2012
02/21/2012
02/21/2012

fltr = "between(dt,{02/01/2012},{02/29/2012})"
? table.external_record_content_get("anames","dtoc(dt)","",fltr)
= 02/21/2012
02/21/2012
02/21/2012

Same results in V8 and V10.

[csda1]

Re: Could This Be a Leap Year Problem?

Hi Steve

Try a filter like this instead
"(medicaid='02 10 097352-01').AND.between(cdate(serv_start),cdate({01/01/2011}),cdate({02/20/2013}))"

Note the extra parenthesis in the 1st term. This guarantees the evaluation order. The cdate() functions puts the between comparing character text, not dates, so may work more reliably.

One issue is that IF alpha is using LQO, and you have an index based on serv_start (at least at the beginning of an index expression) , the index that is being used for LQO could be corrupted. Try rebuilding it (or any using serv_start), and see if the problem goes away.

As for it being a leap year issue, I seriously doubt it. There is no need for leap year value info in your comparisons, and thus Alpha would not be processing anything associated with it.

See also my related date tips here

As to the problem, wrap it up for Alpha, and send it along and maybe they can determine if there is a bug or not.

[G Gabriel]

Re: Could This Be a Leap Year Problem?

Steve:
To narrow down the issue, I would:
1-Try the filter with the date only,
2-Try it with the string part only
3-Then try it with the date before the string, i.e.:
fltr = "between(serv_start,{01/01/2011},{02/20/2013}).AND.medicaid='02 10 097352-01'"
4-If all fail, try to evaluate serv_start before declaring the filter

[Steve Andrews]

Re: Could This Be a Leap Year Problem?

THANK YOU, Ira!!!
... and thanks to Gabe and Stan as well.

I did change the between() to compare the CDATE() values of the dates instead and it finally worked.

From this point on (especially until we get past 2/29/12), I will be very suspicious of date comparisons throughout my applications. I want to eventually find the cause of the problem but do not have time at the momnent to do so. Thanks again.

Steve

[G Gabriel]

Re: Could This Be a Leap Year Problem?

Steve:
For what's worth i just took another look at your code and noticed:

table.external_record_content_get("caregvn","dtoc(serv_start)","",fltr)

Change that to:

table.external_record_content_get("caregvn","dtoc(serv_start)",[COLOR="#FF0000"][B]"serv_start"[/B][/COLOR],fltr)[COLOR="#FF0000"]'or any other field of your choice[/COLOR]

and see what happens.

[Ed Merritt]

Re: Could This Be a Leap Year Problem?

Hi Steve:

I found a similar issue today when I tried to run statements for a my net 30 customers. The statement for one customer showed the orders but no aging and no totals. All other customers did calculate correctly. It took a few hours but I narrowed the problem down to orders with an invoice date of 11/29/2011.

I confirmed this issue by going back to this customer's previous orders and manually changing the amount due so they would be past due. I ran the statement and there were the old order records with the aging calculating correctly.

I should have stopped in here first, but as a long time user of A5, I'd never seen this issue before. I'll have to try the suggestions from Ira and see if this fixes my problem. Thanks for posting this issue.

I'm also using v 10.5.