我有一个包含以下字段的表：
报告(表名)
Rep_Date(日期)
Rep_Time(日期)
Rep_Time字段具有类似’01 / 01/1753 07:30:00’的值,即时间部分是相关的.我写了以下查询：
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,'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不起作用.
解决方法
1.
To_Date(To_Char(MaxDate,'DD/MM/YYYY')) = REP_DATE
造成了这个问题.当你使用没有时间格式的to_date时,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') from dual; TO_DATE(T --------- 26-SEP-12
2.
更重要的是,为什么要转换为char然后到目前为止,而不是直接比较
MaxDate = REP_DATE
如果你想在比较之前忽略MaxDate中的时间组件,你应该使用..
trunc(MaxDate ) = rep_date
代替.
==更新：根据更新的问题.
Rep_Date = 01/04/2009 Rep_Time = 01/01/1753 13:00:00
我认为问题更复杂.如果rep_time只是时间,则不能将其作为日期存储在数据库中.它必须是字符串或日期到时间间隔或数字为秒(感谢Alex,见this).如果可能的话,我建议使用一个包含日期和时间的列rep_date,并将其直接与最大日期列进行比较.
如果它是一个正在运行的系统而你无法控制repdate,你可以试试这个.
trunc(rep_date) = trunc(maxdate) and to_char(rep_date,'HH24:MI:SS') = to_char(maxdate,'HH24:MI:SS')
无论哪种方式,时间存储不正确(从1753年可以看出),可能还有其他问题.
