Oracle：不是有效月份

Question

我有一个包含以下字段的表：

报告（表名）Rep_Date（日期）Rep_Time（日期）

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

select rep_date, rep_time from reports where pid=61 and rownum=1

Rep_Date = 01/04/2009
Rep_Time = 01/01/1753 13:00:00

更新15:58我现在可以执行以下查询：

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

但是，我需要在WHERE子句中再添加一条语句，将MaxDate的时间部分与rep_time:to_date(to_char(MaxDate,'mm/dd/yyyy'),'mm/dd/yyyy')=rep_date不起作用。

Answer 1

1.

To_Date(To_Char(MaxDate, 'DD/MM/YYYY')) = REP_DATE

导致了这个问题。当您使用to_date而不使用time格式时，oracle将使用当前会话NLS格式进行转换，在您的情况下，该格式可能不是“dd/mm/yyyy”。看看这个...

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.

MaxDate = REP_DATE

trunc(MaxDate ) = rep_date

Rep_Date = 01/04/2009 Rep_Time = 01/01/1753 13:00:00

trunc(rep_date) = trunc(maxdate) and 
to_char(rep_date,'HH24:MI:SS') = to_char(maxdate,'HH24:MI:SS')

不管怎样，时间被错误地存储（正如你从1753年可以看出的那样），可能会有其他问题。