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.
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
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.
Source: Stackoverflow.com