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 when inserting a date
Use TO_DATE('2021/07/02', 'YYYY/MM/DD')
to convert your date values to the standard database date format
Replace the above date value as your date columns
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.
Query with error ORA-01843: not a valid month, with data of type DATE
Introduce date
constants using the date
keyword and YYYY-MM-DD format:
DATA_EXPORTACAO BETWEEN DATE '2020-09-14' and DATE '2020-09-19'
Note that in Oracle, the DATE
data type -- despite its name -- also stores a time component. For this reason, I strongly discourage using BETWEEN
. Instead:
DATA_EXPORTACAO >= DATE '2020-09-14' AND
DATA_EXPORTACAO < DATE '2020-09-20'
This works regardless of whether there is a time component.
Oracle SQL TO_TIMESTAMP_TZ Format issue ORA-01843: not a valid month
First, you should fix the data model to store date/time values using the appropriate time stamp which is not a string.
Second, I would return the results using dates:
select trunc(to_date(substr(update, 1, 11), 'DD/MMM/YYYY'), 'MON') as yyyymm,
count(*)
from t
group by trunc(to_date(substr(update, 1, 11), 'DD/MMM/YYYY'), 'MON');
You can, of course, format the first column however you want using to_char()
. However, I want to emphasize that your data and your code should be using date
s or timestamp
s.
Related Topics
How to Persist a Variable Across a Go
Do Indexes Work with "In" Clause
How to Escape Non-Format Characters in Oracle's To_Char
How to Execute a Stored Procedure in a SQL Agent Job
Get List of Computed Columns in Database Table (SQL Server)
Postgres - Create Table from Select
Cumulating Value of Current Row + Sum of Previous Rows
Sql: Is There a Possibility to Convert Numbers (1,2,3,4...) to Letters (A,B,C,D...)
SQL Join, Group by on Three Tables to Get Totals
Iterate Through Rows in SQL Server 2008
Postgresql Generate_Series of Months
MySQL Query to Select Everything Except
Best Way to Get the Next Id Number Without "Identity"
How to Design a Database Schema to Support Tagging with Categories