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
ENAME HIREDATEMac 06-JAN-13
voila, now your actual date format is found.