RList select on older dates (OpenInsight 32-bit)
At 23 MAR 2023 08:57:55AM Nick Stevenson wrote:
Hi
OI9.4.4
We have a btree index on the date of birth field (oconv'd to D4.E)
Btree.extract etc picks up records perfectly.
Trying to do an Rlist select on a date of 07.12.1958.
Rlist select using WITH DATE_OF_BIRTH EQ "07.12.1958" brings no rows back.
Rlist select using WITH DATE_OF_BIRTH BETWEEN "07.12.1958" AND "07.12.1958" brings back the correct records.
Rlist select using WITH DATE_OF_BIRTH BETWEEN "01.12.1958" AND "10.12.1958" brings back the correct records PLUS records outside of that range (like 03.02.1967).
Indexes have been rebuilt. Same outcome over several databases.
It's not just this date, it seems to be any date preceding the 1990's - haven't nailed it down yet.
Also tried using the iconv'd value - no joy.
Any clues?
At 24 MAR 2023 10:30AM Nick Stevenson wrote:
RESOLVED
Just in case anyone has the same problem in the future:
1. The data dictionary had the date field with display length 10, Left justified.
2. Looking at other tables that did not have the problem, I changed the field to length 11, right justified.
3. Then NOTHING resolved to the records - not even btree extract.
4. Rebuilt the btree index - no records.
5. Deleted the btree index and re-added it - BINGO.
Followed the same routine in 4 other databases, success each time.