Why is to_date needed to pull data from Oracle into Crystal Reports

Question

If I run the following select in PL/SQL from my Oracle DB I return expected results:

select * from risk t where t.accounting_to_date='01-JAN-3000'

When I run the same select statement in Crystal Reports 2016 using a command and an Oracle Connection, I cannot pull any data.

The workarounds I have found are:

Use the to_date function in my Crystal Report command like this:

select * from risk t where t.accounting_to_date= to_date('01-JAN-3000','dd-MON-yyyy')

OR

Use an ODBC connection.

Both of these workarounds do work. However, my question is this: What is causing this issue? Is there a setting somewhere that I can change? Is the issue on the Oracle side or the Crystal Reports side? Is this a bug?

I am just trying to wrap my head around why this is happening. I have to use the Oracle Connection, instead of ODBC, so that workaround is not possible for me. Using the to_date around everysingle date either in Oracle or in Crystal is overwhelming. I have had to add this function to dozens of dates already, and there are hundreds more.

I stumbled across this when we changed the way we connect Crystal Reports from using an ODBC connection to an Oracle connection. I found lots of missing data, and it was corrected by using the to_date function.

Thank you in advance.

If accounting_to_date is a date, then comparing it to the string 01-JAN-3000 involves an implicit data conversion. You should always use explicit data conversion for dates. — Jeffrey Kemp
You specify date literals in Oracle as date '3000-01-31' or a more verbose to_date() expression if you really want. '01-JAN-3000' is just a string and you are trusting to the current default settings that it will get converted the way you hope. What if someone runs this from a desktop with a different language setting? — William Robertson

slambeth slambeth · Accepted Answer · 2017-08-31T17:05:26

The default casting of a varchar2 to a date only works for '01-JAN-2017' because oracle nls_date_format is configured with that default format. The default format can be changed per session, which is likely what Crystal Reports does every time it makes a connection.

if you ran this, you'd see what the default was:

SELECT value
FROM   nls_session_parameters
WHERE  parameter = 'NLS_DATE_FORMAT'

If you added that query to your report somehow, you'd probably be able to see the format that crystal reports sets.

You probably shouldn't ever rely on the default format to always work. The DBA can change that format if he chooses, and all code that relied on it will break.

Why is to_date needed to pull data from Oracle into Crystal Reports

2 Answers