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:

1. 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

2. 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.

Answer 1

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.

Answer 2

We had the same issue when using the Oracle ODBC driver in Crystal, and was fixed when we moved to the "CR Oracle Wire Protocol ODBC Driver". Since you need to use an oracle connection, I'm not sure what you'll do though. Here's what we found from SAP:

Info from SAP