水晶报表 - Oracle 01861错误 [英] Crystal Reports - Oracle 01861 Error

问题描述

我有一个水晶报表将日期传递到SQL查询{？Date}

I have a crystal report which passes a date into an SQL query {?Date}

这个工作在水晶通过ODBC连接连接时， Oracle服务器连接我得到一个错误：

This worked when crystal is connected using an ODBC connection however on changing it to a 'Oracle Server' connection I get an error:

ORA-01861: literal does not match format string

我已经尝试删除to_char或to_date（因为研究这似乎是问题的原因，但没有效果） / p>

I have tried removing to_char or to_date (as on research this seems to be the cause of the issue but to no avail)

SELECT
ACTIVE_SEPARATE.WO_NO,
MCH_TYPE,
24 * (to_date(to_char(REAL_F_DATE,'DD-MM-YYYY HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS') - to_date(to_char(REQUIRED_START_DATE,'DD-MM-YYYY HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS')) as BREAKDOWN_HOURS,
(100 - (((24 * (to_date(to_char(last_day(to_date({?Month}, 'dd-mm-yyyy')),'DD-MM-YYYY'), 'DD-MM-YYYY') - to_date(to_char( trunc(to_date({?Month}, 'dd-mm-yyyy'), 'month'),'DD-MM-YYYY'), 'DD-MM-YYYY'))) + 24) - (24 * (to_date(to_char(REAL_F_DATE,'DD-MM-YYYY HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS') - to_date(to_char(REQUIRED_START_DATE,'DD-MM-YYYY HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS')))) / ((24 * (to_date(to_char(last_day(to_date({?Month}, 'dd-mm-yyyy')),'DD-MM-YYYY'), 'DD-MM-YYYY') - to_date(to_char( trunc(to_date({?Month}, 'dd-mm-yyyy'), 'month'),'DD-MM-YYYY'), 'DD-MM-YYYY'))) + 24)) AS PERCENTAGE_AVAILABILITY
FROM ACTIVE_SEPARATE
RIGHT JOIN EQUIPMENT_FUNCTIONAL ON EQUIPMENT_FUNCTIONAL.MCH_CODE = (SELECT DISTINCT CASE WHEN MCH_CODE LIKE '%-%' THEN TRIM(SUBSTR(MCH_CODE, 0, INSTR(MCH_CODE, '-')-1)) ELSE MCH_CODE END FROM ACTIVE_SEPARATE ACTIVE_SEPARATE_SUB WHERE ACTIVE_SEPARATE.MCH_CODE = ACTIVE_SEPARATE_SUB.MCH_CODE)
WHERE ERR_CLASS = '001'
AND MCH_TYPE IS NOT NULL
AND (ACTIVE_SEPARATE.REQUIRED_START_DATE BETWEEN
trunc({?Month}, 'MM')
AND
LAST_DAY({?Month}))

可能是用户输入日期（任何日期）。此代码将采用日期。计算所选月份的总营业时间。

The nitty gritty is that the user enters a date (any date). This code will take the date. Work out the total hours in the selected month.

它还会计算完成时间和开始时间之间的距离。

It will also work out the breakdown hours by taking the finish time and the start time away from each other.

最后，它将在当月的工作时间 - 制动时间给我们可用的时间。

Finally it will work out hours in the month - brakedown hours to give us available hours.

推荐答案

您在处理您的 {？Month} 值时不一致。在您选择的列表中：

You're being inconsistent in how you're treating your {?Month} value. In the select list you're doing:

to_date({?Month}, 'dd-mm-yyyy')

如果Crytal短日期被视为字符串类型，那么这是OK的（虽然使用斜杠而不是破折号be beater），但是如果它被视为日期类型，那么这是隐式转换为字符串，然后显式转换回日期，这意味着您的NLS设置发挥作用。

If the Crytal short date is treated as a string type then this is OK (though using slashes instead of dashes would be beater), but if it's treated as a date type then this is doing an implicit conversion to a string and then an explicit conversion back to a date, which means your NLS settings come into play.

但是在where子句中你只是这样做：

But then in the where clause you just doing:

BETWEEN trunc({?Month}, 'MM') AND LAST_DAY({?Month}))

如果Crytal短日期被视为字符串类型，那么这是隐式转换为日期，这意味着您的NLS设置再次发挥作用，但如果它被视为日期类型，那么这是可以的。

This has the reverse situation. If the Crytal short date is treated as a string type then this is doing an implicit conversion to a date, which means your NLS settings come into play again, but if it's treated as a date type then this is OK.

他们不能都是正确的，我不熟悉水晶报表，所以我不知道哪个转换发生。使它们一致 - 在where子句中添加显式转换，或在选择列表中删除它们 - 将显示哪个是问题。 ORA-01861只能来自 to_date（），这意味着where子句是问题。

They can't both be right, and I'm not familiar with Crystal Reports so I'm not sure which conversion is happening. Making them consistent - either adding explicit conversion in the where clause, or removing it in the select list - will show which is the problem. I think ORA-01861 can only come from to_date(), which means the where clause is the problem.

在SQL Developer中，你的NLS_DATE_FORMAT大概是DD-MM-YYYY，所以隐式转换仍然'工作'，无论哪种方式发生。但在Crystal Reports中，您有一个不同的NLS_DATE_FORMAT，导致隐式转换失败。

In SQL Developer your NLS_DATE_FORMAT is presumably DD-MM-YYYY, so the implicit conversions still 'work', whichever way around they are happening. But in Crystal Reports you have a different NLS_DATE_FORMAT, which is causing the implicit conversions to fail.

这篇关于水晶报表 - Oracle 01861错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！