Oracle not a valid month

Question

I have a table with the following fields   Reports  table name  Rep Date  date  Rep Time  date   The Rep Time field has values like  01 01 1753 07 30 00  i e  the time part is relevant   I have written the following query   select Reports pid  MaxDate from Reports INNER JOIN       select pid  max TO DATE TO CHAR REP DATE   DD MM YYYY              TO CHAR REP TIME   HH24 MI SS     DD MM YYYY HH24 MI SS    As MaxDate     from reports     group by pid   ReportMaxDate on Reports PID   ReportMaxDate PID AND To Date To Char MaxDate   DD MM YYYY      REP DATE WHERE REPORTS PID 61   The derived table part of the query runs  but when I run the entire query I get an error   not a valid month    Why is this    In order to help debug this  if I run the following query   select rep date  rep time from reports where pid 61 and rownum 1   I get   Rep Date   01 04 2009 Rep Time   01 01 1753 13 00 00   UPDATE 15 58 I am now able to execute the following query   select Reports pid  MaxDate from Reports INNER JOIN       select pid  max TO DATE TO CHAR REP DATE   DD MM YYYY              TO CHAR REP TIME   HH24 MI SS     DD MM YYYY HH24 MI SS    As MaxDate     from reports group by pid   ReportMaxDate on Reports PID   ReportMaxDate PID AND to date to char maxdate  MM DD YYYY    MM DD YYYY     REP DATE WHERE REPORTS PID 61   However  I need to add one more statement to the WHERE clause comparing the time part of MaxDate to rep time  to date to char maxdate  MM DD YYYY    MM DD YYYY     REP DATE does not work

User · Accepted Answer

1   To Date To Char MaxDate   DD MM YYYY      REP DATE   is causing the issue  when you use to date without the time format  oracle will use the current sessions NLS format to convert  which in your case might not be  DD MM YYYY   Check this     SQL gt  select sysdate from dual   SYSDATE --------- 26-SEP-12  Which means my session s setting is DD-Mon-YY  SQL gt  select to char sysdate  MM DD YYYY   from dual   TO CHAR SY ---------- 09 26 2012   SQL gt  select to date to char sysdate  MM DD YYYY    from dual  select to date to char sysdate  MM DD YYYY    from dual                  ERROR at line 1  ORA-01843  not a valid month  SQL gt  select to date to char sysdate  MM DD YYYY    MM DD YYYY   from dual   TO DATE T --------- 26-SEP-12   2   More importantly  Why are you converting to char and then to date  instead of directly comparing   MaxDate   REP DATE   If you want to ignore the time component in MaxDate before comparision  you should use    trunc MaxDate     rep date   instead     Update   based on updated question   Rep Date   01 04 2009 Rep Time   01 01 1753 13 00 00   I think the problem is more complex  if rep time is intended to be only time  then you cannot store it in the database as a date  It would have to be a string or date to time interval or numerically as seconds  thanks to Alex  see this    If possible  I would suggest using one column rep date that has both the date and time and compare it to the max date column directly   If it is a running system and you have no control over repdate  you could try this    trunc rep date    trunc maxdate  and  to char rep date  HH24 MI SS     to char maxdate  HH24 MI SS     Either way  the time is being stored incorrectly  as you can tell from the year 1753  and there could be other issues going forward

User · Answer

You can also change the value of this database parameter for your session by using the ALTER SESSION command and use it as you wanted ALTER SESSION SET NLS DATE FORMAT    DD-MM-YYYY   SELECT TO DATE  05-12-2015   FROM dual   05 12 2015

User · Answer

To know the actual date format  insert a record by using sysdate  That way you can find the actual date format  for example  insert into emp values 7936   Mac    clerk   7782  sysdate  1300  300  10     now  select the inserted record   select ename  hiredate from emp where ename  Mac     the result is   ENAME   HIREDATE Mac     06-JAN-13   voila  now your actual date format is found

[sql] Oracle: not a valid month

Examples related to sql

Examples related to oracle