Reputation: 156
So at the query level, I have it:
to_char(
(
to_date(
substr(TIMESTAMP, 1, 19),
'yyyy-mm-dd hh24:mi:ss'
)
),
'dd-mon-yyyy hh24:mi:ss'
) as DateTime,
And I've tried looking at a few articles with one most notable:
How to change the date format in Oracle BI Publisher?
I have also tried using:
and trunc(TIMESTAMP) between :FROM_DATE AND :TO_DATE
--and also
and trunc(TIMESTAMP) between to_date(:FROM_DATE, 'yyyy-MM-dd') AND to_date(:TO_DATE, 'yyyy-MM-dd')
While going through structure and XML I noticed my date is in string format:
element name="DATETIME" value="DATETIME" label="DATETIME" dataType="xsd:string" breakOrder="ascending" fieldOrder="3"
So I removed the to_char to get the date format
The error I've been getting is:
java.sql.SQLDataException: ORA-01843: not a valid month
How do I fix this issue?
EDIT: Format for the column, TIMESTAMP, the format is CHAR(14) Example of values is like 20200701103038 It runs perfectly in SQL Developer
Upvotes: 1
Views: 40786
Reputation: 8528
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
.
Upvotes: 3
Reputation: 143163
Just to make sure what SYSDATE
(I'm going to select) represents:
SQL> alter session set nls_Date_format = 'dd.mm.yyyy';
Session altered.
Today is:
SQL> select sysdate from dual;
SYSDATE
----------
26.07.2020
This is the way to get the error you got: apply wrong format mask to a string which represents a DATE value:
SQL> select to_Date('2020-27-07', 'yyyy-mm-dd') from dual;
select to_Date('2020-27-07', 'yyyy-mm-dd') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
SQL>
How to fix it? Usually, it is hard to fix it if dates are represented as strings. They (strings that represent dates) are like a box of chocolates, you never know what you're gonna get. If there's at least one wrong value, query will fail.
How to find wrong values? You could create a function which returns TRUE (or 1 or whatever you want) if a string you pass to it represents a valid date format. But, if you pass 01/02/03
, which is which? Different formats match (e.g. dd/mm/yy
, yy/mm/dd
, mm/yy/dd
...). Worse cases are 84/25/32
or AB/23/2f
. They are all strings, they "match" two characters separated by slash but certainly aren't valid dates, so you can't rely on a simple regular expression.
Shortly, there's no easy nor fast way out of it.
Upvotes: 3