Oracle SQL/PL - ORA-01843: not a valid month-CodePudding

I get the error message: ORA-01843: not a valid month after executing a sql plus script.
I try using the "standard" date format yyyy-mm-dd.
Is SQL/PL not understanding the alter session statement?

set linesize 200
set pagesize 1000
alter session set NLS_NUMERIC_CHARACTERS = ',.';
alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd';

select
 *
from my_table
where  
date >= '2019-08-31';

exit

CodePudding user response：

What you do need - from my point of view - is not to compare date values to strings.

Presuming that date here actually represent a DATE datatype column (why didn't you post table description?) (as already commented, you can't name a column that way, not unless you enclosed its name into double quotes), then

where date >= '2019-08-31'
      ----    ------------
      DATE     this is a string
    datatype

Use date literal, which always has a DATE keyword and date in format 'yyyy-mm-dd':

where date >= date '2019-08-31'

Or, use to_date function with appropriate format mask:

where date >= to_date('2019-08-31', 'yyyy-mm-dd')

If date column (wrong name, as we already know) actually contains strings and you hope all of them are following the 'yyyy-mm-dd' format, well, some values don't. Storing dates into varchar2 datatype column is almost always a bad idea. Nobody prevents you from storing e.g. '2019-ac-31' into it, and that isn't a valid date value.