Oracle: not a valid month Oracle: not a valid month sql sql

Oracle: not a valid month


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> select sysdate from dual;SYSDATE---------26-SEP-12Which means my session's setting is DD-Mon-YYSQL> select to_char(sysdate,'MM/DD/YYYY') from dual;TO_CHAR(SY----------09/26/2012SQL> 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 monthSQL> select to_date(to_char(sysdate,'MM/DD/YYYY'),'MM/DD/YYYY') from dual;TO_DATE(T---------26-SEP-12


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


==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.

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


voila, now your actual date format is found.

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