Re: Searching on a date

On Wed, 28 Feb 2001 16:23:31 +1100, "Peta Griffith" <peta_at_dataspeed.com.au> wrote:

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

Your NLS_DATE_FORMAT setting is different from the implicit format mask you use in the date string. Either use an explicit format mask (recommended) or change your NLS_DATE_FORMAT. The default date format is DD-MON-YY, in your case it might be DD-MM-YY

Hth

Sybrand Bakker, Oracle DBA Received on Wed Feb 28 2001 - 00:34:42 CST