Re: Searching on a date

Hi!

Is this "table" really a view? Could it be a problem in it? Is CO02DATA.TBLBPSTUDENTS.DATE_OF_ADDRESS_CHANGE really DATE? Could you try to remove the Where condition and see if it still failes?

Another thought (about something you have not asked about :-) mind that the date column in Oracle contains a time-part. If your column DATE_OF_ADDRESS_CHANGE is stored with the time-part != 00:00:00
this where clause might not behave as you expect. Because TO_DATE('18991229','YYYYMMDD') = TO_DATE('18991229000000','YYYYMMDDHH24MISS') Easiest/less readable:-) solution is to use TRUNC on the column to get rid of the time part (setting it to 00:00:00 that is)

Typically you get this kind of surprise if you populate your column with SYSDATE Frank!

Peta Griffith <peta_at_dataspeed.com.au> wrote in message news:KW%m6.5163$v5.18574_at_newsfeeds.bigpond.com...
> We are using the Oracle ODBC driver and Visual Basic and are trying either
> of the 2 following statements :
>
> Statement 1:
>
> SELECT CO02DATA.TBLBPSTUDENTS.STUDENT_NUMBER,
> CO02DATA.TBLBPSTUDENTS.SCHOOL_NUMBER, CO02DATA.TBLBPSTUDENTS.SURNAME,
> CO02DATA.TBLBPSTUDENTS.CHRISTIAN_NAMES,
> CO02DATA.TBLBPSTUDENTS.DATE_OF_ADDRESS_CHANGE,
> CO02DATA.TBLBPSTUDENTS.CURR_ADDRESS1,
 CO02DATA.TBLBPSTUDENTS.CURR_ADDRESS2,
> CO02DATA.TBLBPSTUDENTS.CURR_NO_OF_SECTIONS1,
> CO02DATA.TBLBPSTUDENTS.CURR_NO_OF_SECTIONS2,
> CO02DATA.TBLBPSTUDENTS.LAST_ADDRESS1,
 CO02DATA.TBLBPSTUDENTS.LAST_ADDRESS2,
> CO02DATA.TBLBPSTUDENTS.YEAR_OF_ADDRESS_CHANGE,
> CO02DATA.TBLBPSTUDENTS.SEMESTER_OF_ADDRESS_CHANGE FROM
> CO02DATA.TBLBPSTUDENTS WHERE

 (CO02DATA.TBLBPSTUDENTS.DATE_OF_ADDRESS_CHANGE)
> <> '29/DEC/1899'
>
> Statement 2:
>
> SELECT CO02DATA.TBLBPSTUDENTS.STUDENT_NUMBER,
> CO02DATA.TBLBPSTUDENTS.SCHOOL_NUMBER, CO02DATA.TBLBPSTUDENTS.SURNAME,
> CO02DATA.TBLBPSTUDENTS.CHRISTIAN_NAMES,
> CO02DATA.TBLBPSTUDENTS.DATE_OF_ADDRESS_CHANGE,
> CO02DATA.TBLBPSTUDENTS.CURR_ADDRESS1,
 CO02DATA.TBLBPSTUDENTS.CURR_ADDRESS2,
> CO02DATA.TBLBPSTUDENTS.CURR_NO_OF_SECTIONS1,
> CO02DATA.TBLBPSTUDENTS.CURR_NO_OF_SECTIONS2,
> CO02DATA.TBLBPSTUDENTS.LAST_ADDRESS1,
 CO02DATA.TBLBPSTUDENTS.LAST_ADDRESS2,
> CO02DATA.TBLBPSTUDENTS.YEAR_OF_ADDRESS_CHANGE,
> CO02DATA.TBLBPSTUDENTS.SEMESTER_OF_ADDRESS_CHANGE FROM
> CO02DATA.TBLBPSTUDENTS WHERE

 (CO02DATA.TBLBPSTUDENTS.DATE_OF_ADDRESS_CHANGE)
> <> TO_DATE('29/DEC/1899')
>
> We are getting the error message :
>
> 'ORA-01858 Non numeric character was found where a numeric was expected'
>
> for both statements.
>
> Does anyone have any ideas of why this is happening??
>
> Thank you very much.
>
>
Received on Wed Feb 28 2001 - 18:26:56 CET