DateTime selection inconsistency

[farside11]

Hi there,

I use Crystal Reports and Crystal Enterprise v10, reporting from an Oracle 9i database in a Unix environment.

For this report, I have a single table data source that contains records of jobs run. Each record contains the job's Start_DateTime and the Finish_DateTime - however, they exist in the table as string values.

Simply converting these values using CDateTime() - and then using a DateTime range parameter to drive the record selection on the Start_DateTime - works a treat in the Crystal Reports environment.

However, as soon as I publish it to CE and run it there, I get inconsistencies in the records returned. By the way, the time value is important because I need to report on a period beginning on one day at 7am and ending 24 hrs later at 7am. Eg. 07-Aug-06 (7am) to 08-Aug-06 (7am).

The symptom I got today, using the above example, was that in CR, I correctly received 7 records based on the 24 hr period. Running it in CE, however - against the very same database - only returned 1 records. I tried extending the parameter value and found that when I went back as far as 11pm on 06-Aug-06 to 7am on 08-Aug-06, it returned an extra record for 07-Aug-06 at 0:04am. This still means that it skipped 6 records that all have a Start_DateTime of between 07-Aug-06 8:43am to 8:04pm - that DO show up in the version run on Crystal Reports. Bizarre!

Please note that I have double checked that:
a) they were run against the same data source
b) these symptoms have been verified at least 3 or 4 times, so the data has not changed between runs.

If anyone has any experience with this behaviour, or any advice, it would be much appreciated.

Many thanks,
Mike
(Sydney, Australia)

 

[Madawc]

Please post your selection command. There's probably a flaw in it.

You could also take the commands and turn them into Boolian formula fields, checks without an IF that return True or False. Do a test version without selection but wiht the boolians next to the data to see how the test is actually working.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Turkbear]

Hi,
What database connection method are you using for the CE server? ( If not using the native Oracle Server, change to it).

Why would you have DateTime data stored in a Varchar ( or worse yet char) datatype field?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Instead of assuming that a CDATE will work from within Crystal, use the to_date function in a SQL Expression and reference the SQL Expression in the record selection.

Different machines may use a diffferent default date format, which may be your issue, or as Turk suggested a bad connectivity type is being used, use Native which is listed as Oracle Server, not an ODBC or OLE DB connection.

-k

 

[farside11]

Thanks for the feedback everyone.

Answers to issues raised:
1. Database connection used in all cases is Oracle native.

2. Why varchar? Unfortunately, programmers did not consult reporting team before development (sound familiar? grrr!) and it's now a relatively big deal to get it changed - maybe my last option however.

3. I'm trying the SQL expression angle, but cannot get Crystal to accept the formaula TO_DATE('SCHEDULER_HIST'.'START_TIME_VAR',DD-MM-YYYY HH24:MI:SS') - yes, I'm using SQL Expression, not the formula editor. The 'FORMAT' I have shown is the exact format of the imput string - eg. 14-02-2005 22:00:25 - is this correct?

4. Here is the syntax for my record selection: if {?Date Range} = "Yesterday"
then {@Start_Date_Time} in DateTime (CurrentDate - 1, Time("07:00:00")) to DateTime (CurrentDate, Time("07:00:00"))
else {@Start_Date_Time} = {?From Time}
The parameter {?From Time} is defined as a DateTime range and the parameter {?Date Range} is a string, with either "Yesterday", or "Custom Date" as options.

5. I did try the Boolean formula idea and it confirmed the fact that it correctly produces 7 records in CR, but only 1 in CE. The "False" shows up on the "missing" records when I print from CE.

Many thanks for your comments. I will try to investigate the "date formats on the servers" angle. However, this issue is a first, my team having written almost 150 reports already in this same environment - with no similar issues. I fear that I may have to convince the programmers to change the data type to a proper Date!

Thanks again,
Mike