甲骨文:无效月份 [英] Oracle: not a valid month

问题描述

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

I have a table with the following fields:

报告(表名) Rep_Date(日期) Rep_Time(日期)

Reports (table name) Rep_Date (date) Rep_Time (date)

Rep_Time字段的值类似于"01/01/1753 07:30:00"，即时间部分是相关的.我写了以下查询:

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

我得到:

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

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

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

但是，我需要在WHERE子句中再添加一条语句，以比较MaxDate和rep_time的时间部分:to_date(to_char(maxdate,'MM/DD/YYYY'),'MM/DD/YYYY') = REP_DATE不起作用.

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.

推荐答案

1.

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

是引起问题的原因.当您使用不带时间格式的to_date时，oracle将使用当前会话的NLS格式进行转换，在您的情况下，该格式可能不是"DD/MM/YYYY".检查一下...

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.

更重要的是，为什么要转换为char然后再转换为日期，而不是直接进行比较

More importantly, Why are you converting to char and then to date, instead of directly comparing

MaxDate = REP_DATE

如果要在比较之前忽略MaxDate中的时间分量，则应使用..

If you want to ignore the time component in MaxDate before comparision, you should use..

trunc(MaxDate ) = rep_date

相反.

==更新:基于更新的问题.

==Update : based on updated question.

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

我认为问题更加复杂.如果rep_time仅是时间，则不能将其作为日期存储在数据库中.它必须是时间间隔的字符串或日期，或者必须是以秒为单位的数字(感谢Alex，请参阅

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.

如果它是一个正在运行的系统，并且您无法控制repdate，则可以尝试此操作.

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')

无论哪种方式，时间存储都不正确(正如您可以从1753年看到的那样)，并且可能还会出现其他问题.

Either way, the time is being stored incorrectly (as you can tell from the year 1753) and there could be other issues going forward.

这篇关于甲骨文:无效月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！