Crystal Reports - Oracle 01861 Error

Question

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

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

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.

Its going to be very difficult to troubleshoot this ugly code. Does it give a line number for the error? Can you upload some sample data? We need to see what it starts with if we are going to have to convert the date 3 times for 1 value. — mmmmmpie
No line number. I know the codes ugly, the schema I'm working with is dreadful. I will edit now. — LaLa
Is this part of a much larger view or query? Break out some of these pieces and run them distinctly. For example execute this: select to_char(REAL_F_DATE,'DD-MM-YYYY HH24:MI:SS') from HISTORICAL_SEPARATE; repeat this out until you are running the whole pieces. — mmmmmpie
Its not part of anything bigger. The funny thing is, it would run in Oracle SQL Dev but when running it from crystal it wouldn't. — LaLa

Alex Poole Alex Poole · Accepted Answer · 2015-02-23T14:42:57

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

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.

But then in the where clause you just doing:

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

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.

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.

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.

Crystal Reports - Oracle 01861 Error

1 Answers