SQL "Not a Valid Month"

SQL not a valid month

It's not entirely clear which you wanted, so you could try:

• For month-day-year format:

  INSERT INTO Reserves VALUES(22, 101, TO_DATE('01-01-1998','MM-DD-YYYY'));

• For day-month-year format:

  INSERT INTO Reserves VALUES(22, 101, TO_DATE('01-01-1998','DD-MM-YYYY'));

Also, recommended reading: Oracle functions: TO_DATE

How do I fix ORA-01843: not a valid month?

Well, it is quite a bad and extended practice to store DATES as strings, either using varchar2 or char. Anyway, having say that, I think you have a problem with your settings or the way you are constructing your query:

SQL> alter session set nls_date_format='YYYYMMDDHH24MISS' ;

Session altered.

SQL> select to_date('20200726123722') from dual ;

TO_DATE('20200
--------------
20200726123722

SQL> select sysdate from dual ;

SYSDATE
--------------
20200726124622

Besides, as you said, if your data is stored as YYYYMMDDHHMISS, you are applying the wrong date mask YYYY-MM-DD HH24:MI:SS to that char. I would use CAST to define the field as DATE.

Example

SQL> create table my_test ( c1 char(20) ) ;

Table created.

SQL> insert into my_test values ('20200726123722') ;

1 row created.

SQL>  insert into my_test values ('20200725123722') ;

1 row created.

SQL> commit ;

Commit complete.

SQL>  alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select cast(c1 as date) from my_test ;

CAST(C1ASDATE)
-------------------
2020-07-26 12:37:22
2020-07-25 12:37:22

SQL>

Update

If you can't change your NLS Session settings, then you must apply a TO_CHAR to the resulting output. But in your case, you want to operate with dates, so as long as it is a date value you want to operate with, you can forget about the mask.

SQL> col value for a20
SQL> select value from nls_database_parameters where parameter = 'NLS_DATE_FORMAT' ;

VALUE
--------------------
DD-MON-RR

SQL> select cast(to_date('20200725123722','YYYYMMDDHH24MISS') as date) from dual ;

CAST(TO_D
---------
25-JUL-20

SQL> select to_char( cast(to_date('20200725123722','YYYYMMDDHH24MISS') as date) , 'YYYYMMDDHHMISS' ) from dual ;

TO_CHAR(CAST(T
--------------
20200725123722

SQL> select case when cast(to_date('20200725123722','YYYYMMDDHH24MISS') as date)  > sysdate
  2  then 'FALSE'
  3  else
  4  'TRUE'
  5  end as result from dual ;

RESUL
-----
TRUE

SQL>

So, if you want to compare the date to another date, don't use to_char. If you want to show the value in a specific format, when you have no option to change the settings, then use to_char.

ORA-01843: not a valid month with valid SQL query

You are calling TO_DATE( date_string, format_model ) with a DATE (not a String) which causes Oracle to do an implicit conversion of the DATE to a VARCHAR2 using the NLS_DATE_FORMAT session parameter as the format model just so you can convert it back to a DATE.

So the query:

TO_DATE(CURRENT_DATE,'MM/DD/YYYY')

Is effectively:

TO_DATE(
  TO_CHAR(
    CURRENT_DATE,
    ( SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT' )
  ),
  'MM/DD/YYYY'
)

If the NLS_DATE_FORMAT matches MM/DD/YYYY then your query will work - the format does not match so it does not work.

What you should be doing is not using TO_DATE on a DATE data type - it is already a DATE so you don't need to.

declare
    date_value        char(10) := TO_CHAR( CURRENT_DATE, 'DD.MM.YYYY' );
    week_value        pls_integer := TO_NUMBER(TO_CHAR(CURRENT_DATE,'WW'));
    start_date_value  char(10) := TO_CHAR(TRUNC(CURRENT_DATE, 'IW'),'DD.MM.YYYY');
    end_date_value    char(10) := TO_CHAR(NEXT_DAY(TRUNC(CURRENT_DATE,'IW'),'SUNDAY'),'DD.MM.YYYY');
begin 
    htp.p('<< '||'Week'|| week_value ||' >> '|| start_date_value ||' - '|| end_date_value);
end;

Getting this error with sql, not a valid month

As others commented on your question, You should never store date as varchar. You can solve your problem in this way:

    Declare
        bonus number(6,2);
        emp_id number(6);
        hiredate varchar2(50);
    
    Begin
        select hire_date into hiredate from employees where emp_id = 3;
        
        if to_date(hiredate,'DD-MON-YYYY','NLS_DATE_LANGUAGE = American') > to_date('01-jan-1998', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American') then
            bonus := 500;
        elsif to_date(hiredate ,'DD-MON-YYYY','NLS_DATE_LANGUAGE = American') > to_date('01-jan-1996','DD-MON-YYYY','NLS_DATE_LANGUAGE = American') then
            bonus := 1000;
        else 
             bonus := 1500;
        end if;
    
        dbms_output.put_line('Bonus for employee with ID ' || emp_id || ' is: ' || bonus );
    
    End;

Update: new solution.

Also, you can do it in this way:

drop table employees;

create table employees (emp_id number(10), emp_name char(10), hire_date date, salary varchar(50));

insert into employees (emp_id, emp_name, hire_date, salary) values (1, 'Ramesh',  to_date('12-JAN-1998','DD-MON-YYYY','NLS_DATE_LANGUAGE = American'), 5000.00);
insert into employees (emp_id, emp_name, hire_date, salary) values (2, 'Suresh', to_date('02-MAR-1997','DD-MON-YYYY','NLS_DATE_LANGUAGE = American'), 5500.00);
insert into employees (emp_id, emp_name, hire_date, salary) values (3, 'Kaushik', to_date('19-JUN-1999','DD-MON-YYYY','NLS_DATE_LANGUAGE = American'), 5000.00);
insert into employees (emp_id, emp_name, hire_date, salary) values (4, 'Mohan', to_date('05-AUG-2001','DD-MON-YYYY','NLS_DATE_LANGUAGE = American'), 6500.00);

Declare
    bonus number(6,2);
    emp_id number(6);
    hiredate date;

Begin
    select hire_date into hiredate from employees where emp_id = 3;
    
    if hiredate > to_date('01-jan-1998', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American') then
        bonus := 500;
    elsif hiredate > to_date('01-jan-1996','DD-MON-YYYY','NLS_DATE_LANGUAGE = American') then
        bonus := 1000;
    else bonus := 1500;
    end if;

    dbms_output.put_line('Bonus for employee with ID ' || emp_id || ' is: ' || bonus );

End;

and result will be:

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

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.

---

Related Topics