Hi Hilmar,

I was thinking of this problem as well and I think my solution could be simplified this way:

lnMonth1 = month(ldStart)
lnDay1 = day(ldStart)

lnMonth2 = month(ldEnd)
lnDay2 = day(ldEnd)

if year(ldEnd) > year(ldStart)
   lnDiff = 1
else
   lnDiff = 0
endif

select * from People where DOB is not null and DOB<> {} and DOB ;
between date(year(DOB),lnMonth1,lnDay1) and date(year(DOB) + lnDiff, lnMonth2, lnDay2)

>>>>

>>>>
>>>>select * from People where DOB between 
>>>>date(year(DOB), month(ldStart), day(ldStart)) and date(iif(year(ldEnd)>year(ldStart), year(DOB+1),year(DOB)), month(ldEnd), day(ldEnd))

>>>>
>>>>from the top of my head (not tested).
>>>
>>>This is similar to David's suggestion. On the other hand, I don't see how this code would be optimized, if Visual FoxPro has to extract the year() for every record to do the comparison. In other words, the expression on the left is a field, but the expression on the right is not constant.
>>
>>Run it with SYS(3054,1) and index on the DOB and tell me the result.
>
>I have decided that for simplicity, I'll do the following - the ideas occured to me while reading through the differente messages in this thread, including your messages, of course (the basic idea of converting to a single year):
>

Get the last four digits from dtos() in each case, and use that for comparison for all dates.
>

Don't give the user the option to select dates over year-end.
>
>Optimization is really quite irrelevant in this case (although it can be achieved with an appropriate index).
>
>My solution takes care of Feb. 29 (which can easily occur at least once if we have an estimated 1000 students).
>
>It does not take care of the end-of-year rollover - which I am avoiding by not giving the user this option. If year-end is a problem for anybody reading this, it can be achieved by combining two searches. For example, your your date range is from {^2009-12-28} to {^2010-01-03}, you would have to do two searches, (1) right(dtos(table.dob),4) between "1228" and "1231", and (2) right(dtos(table.dob),4) between "0101" and "0103". In the digits between quotation marks, "0101" (Jan. 1) and "1231" (Dec. 31) are constant, while "1228" and "0103" must be converted to expressions, also with right(dtos(...)).

If it's not broken, fix it until it is.


My Blog