Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams 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.
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
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, 2012 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, 2012 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, 2012 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, 2012 at 14:57
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.
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
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.