RList select on older dates (OpenInsight 32-bit) [Revelation On-Line Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
{{tag>category:"OpenInsight 32-bit" author:"Nick Stevenson"}}

[[https://www.revelation.com/the-works|Join The Works program to have access to the most current content, and to be able to ask questions and get answers from Revelation staff and the Revelation community]]


==== RList select on older dates (OpenInsight 32-bit) ====
=== At 23 MAR 2023 08:57:55AM Nick Stevenson wrote: ===
 
 
<QUOTE>
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?
</QUOTE>
----

=== At 24 MAR 2023 10:30AM Nick Stevenson wrote:  ===

<QUOTE>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.

</QUOTE>


[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=WORKS_READ&SUMMARY=1&KEY=9C64F6263823984BB53F7950723BE606|View this thread on the Works forum...]]