>>>>Excuse me for asking a stupid question, but did you also SET CENTURY ON?
>>>
>>>set century to 19 rollover 60 implicitly sets century on.
>>
>>No, it does NOT.
>
>You are right, my aplogies. However I confirmed that CENTURY is on. I think I found the problem. The legacy application that inserts the data does not have CENTURY on. Therefore, the index the index can't match my expression. If I reindex the table and try the same seek, everything works as expected.
If you are using dates in an index expression, you should be using DTOS(). This doesn't care if SET CENTURY is ON or OFF as the format is always YYYYMMDD.