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.

score 29 · Accepted Answer · edited May 23 '17 at 12:02

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> select sysdate from dual;

SYSDATE
---------
26-SEP-12

Which means my session's setting is DD-Mon-YY

SQL> select to_char(sysdate,'MM/DD/YYYY') from dual;

TO_CHAR(SY
----------
09/26/2012


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

In answer to your question MaxDate is a DateTime and REP_DATE is a Date. What does trunc(MaxDate ) = rep_date DO? — w0051977, Sep 26 '12 at 14:34
trunc(date) would remove the time component (actually, it makes the time component zero, which is the case with a date column such as REP_DATE. There is no such thing as just date in Oracle.) — Rajesh Chamarthi, Sep 26 '12 at 14:38
+1, thanks it works. Could you explain how to get the time component of a datetime before I accept? to_date(to_char(maxdate,'HH24:MI:SS'),'HH24:MI:SS') does not seem to work. — w0051977, Sep 26 '12 at 14:48
The to_char part is working in your query.The problem is you are trying to convert just the time (10:55:30 for example) into a date. As If you just want to store time, you'll have to do it in a string (varchar2) data type. — Rajesh Chamarthi, Sep 26 '12 at 14:57
@RajeshChamarthi - just to be pedantic, a time on its own doesn't *have* to be a string, it could be stored numerically as seconds, or [as an interval](http://stackoverflow.com/a/12216981/266304), or a time on a fixed date (which the OP already seems to be doing; using 1753 is curious, but then so is using separate date and time fields in the first place). All have conversion implications though. — Alex Poole, Sep 26 '12 at 15:12

score 3 · Answer 2 · edited Apr 18 '20 at 02:49

3

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.

edited Apr 18 '20 at 02:49

jeppoo1

650
1
10
23

answered Jan 06 '13 at 21:52

Devarajan Sekaran

67
1
1
8

score 1 · Answer 3 · answered Nov 23 '20 at 05:09

1

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

answered Nov 23 '20 at 05:09

Mashrabbek Akbarov

296
3
12

score 0 · Answer 4 · answered May 11 '22 at 18:10

0

You can customize as you wish the format inside the ''

SELECT TO_CHAR(column_name, 'MONTH-DAY-yy') FROM table_name;

answered May 11 '22 at 18:10

KR93

1,098
11
10

Oracle: not a valid month

4 Answers4