0

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

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

Ajay2707
  • 5,690
  • 6
  • 40
  • 58
Leigh
  • 1
  • 1
  • 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 Sep 01 '17 at 03:06
  • 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 Sep 01 '17 at 12:39

2 Answers2

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.

slambeth
  • 887
  • 5
  • 17
  • Another way around this was we created a materialized view in Oracle. When we create a report in Crystal and pull from the materialized view we see all the data. Even when the date in the view is not using the to_date function. However, if we pull straight from the view we run into issues. The field is a State Code (ME for Maine) and in order to get that code, we are calling a Function within the view. In that Function there is a date in the Where clause. If we don't use the to_date function around that date, the State Code field is blank. Can someone explain this to me? – Leigh Sep 05 '17 at 14:31
  • Did your view include include the where clause? – slambeth Sep 05 '17 at 14:34
  • The automatic casting to date may work inside compiled code because it will use the nls_date_format settings based on what is current at the time it was compiled (such as a materialized view), not when it is run. Bottom line '01-JAN-2017' is not a date, it is a character string. The fact that it sometimes works as a date is a coincidence. The only thing you can rely on is using to_date(). – slambeth Sep 05 '17 at 14:41
  • The view calls a Function and it is in the Function where the date is in the Where Clause. I guess my thought was that Oracle would do all the processing and then Crystal would just pull the values... but it doesn't seem to work that way. Because you do not need to use the to_date when you are running the view in Oracle, it's only when you are using Crystal Reports. – Leigh Sep 05 '17 at 14:56
-1

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

Gary_W
  • 9,933
  • 1
  • 22
  • 40