Search code examples
sqloracle-databasedatenls

ORA-01861: literal does not match format string 01861

I have given query for which i am getting error as SQL Error: ORA-01861: literal does not match format string 01861. ORD_DEL_DATE is date data type and the value is stored 27-SEP-2017 12-00-00 in this column. Its not possible for me to change the NLS setting so is it possible if i can change in the query and make it run.

SELECT * from Report_Result
    WHERE ORD_DEL_DT >= TO_CHAR( TRUNC ( SYSDATE , 'IW' ) - 7 , 'YYYYMMDD' )
and ORD_DEL_DT <  TO_CHAR ( TRUNC ( SYSDATE , 'IW' ), 'YYYYMMDD' )
Solution

  • The right-hand side returns something like '20171009', an eight-digit VARCHAR2 string. Since the left-hand side is a date, Oracle tries to convert this string to a date, using the NLS date parameter, and it fails because the string doesn't match the NLS setting.

    Why are you working with strings at all? If ORD_DEL_DT is a date, compare it to trunc(sysdate, 'iw') - 7 and trunc(sysdate, 'iw').