CR 9 converts my date fields to strings. Make it stop.

[sirius1026]

I have a data base generated from a Synergy based application. When using Crystal Reports 8.5, everything works fine. I installed CR 9. When I browse the data in the date fields, CR 9 looks at the date data as a six character string rather than as a date. As a result, I can't build any reports where date fields are required. When I run a report built in 8.5, it tells me that my data base file has changed and will proceed to fix up the report. It then converts the dates to the six character strings. Obviously that will not work. Any suggestions?

 

[synapsevampire]

Hmmmm, I hadn't heard this one yet, sounds like a bug.

Try creating a SQL Expression to convert it from string to date.

Or create a formula that converts it.

There may even be a hotfix that will help, are you sure that no other files were changed, such as you ODBC driver, or the database?

-k

 

[sirius1026]

Thanks for responding synapsevampire.

I already added at the hot fixes. No luck.

I know that that the database and the driver have not changed because everything still works just fine when I run it in 8.5.

8.5 shows the date as 3/11/1963

9.0 shows the date as 3/11/1 (Because it thinks the field is a 6 character string) If I change the regional settings for a date via the Control Panel to show a date of YYYY/MM/DD, 9.0 shows it as 1963/3. That test shows that the data is all there, but 9.0 is forcing it to show as if it were a six digit string. Since it only sees six digits, an SQL Expression or formula will not convert it. I have to figure out why 9.0 looks at it as a string rather than as a date.

 

[JEX]

This sounds to me like CR9 is still reading it as a date field. Have you checked the default formatting options set up for the Date fields? This may sound really simple - no offence meant - but are you sure the field is long enough to display it properly?

J

 

[dgillz]

I do not agree with your statement that CR is seeing this as a string.

When you look at the field with the field explorer, and the top right icon is depressed, it should show the field and the data type and length to the right. What does is show for your date field?

Another test would be to create a formula field and make the value "Date(2003,4,1)" and place it on the report and see how it displays. If it displays the same as your databsae field which is a date, then there is an issue on how you have chosen to display dates.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[sirius1026]

Thanks for your thoughts JEX & dgillz. First of all JEX, no offense taken. Its always a good idea to go back to the basics when trying to solve a problem. I had checked to make sure that the field was long enough to display the field properly. No luck. As to the field display dgillz, that is how I knew that CR 9 was seeing it as a String [6]. When I bring up the same field in CR 8.5 it shows that it is a Date. I don't know why it switches. When bring up a report in CR 9 that I created in 8.5, before I refresh the data it show the field as being a date, when I browse it shows it being a date, but when I run it in CR 9, I get a message that says "The database file "xxxxx" has changed. Proceeding to fix up

 

[sirius1026]

Thanks for your thoughts JEX & dgillz. First of all JEX, no offense taken. It’s always a good idea to go back to the basics when trying to solve a problem. I had in fact checked to make sure that the field was long enough to display all the data in the field properly. No luck. I also made sure that the default date settings were the same in CR 9 as they were in 8.5.

As to the field display dgillz, I know that CR 9 sees the field as a string field because I have the view “field type” turned on it CR 9 and it says that it is a String [6]. I can’t figure out why when I bring up the same database, table and field in CR 8.5 it shows that the field is a Date field and the report runs just fine. I don't know why it switches when I run the data via CR 9. When bring up a report in CR 9 that I created in 8.5, before I refresh the data it show the field as being a date field and even when I browse the data it continues to show it as a date. But when I run that 8.5 report in CR 9, I get a message that says "The database file "xxxxx" has changed. Proceeding to fix up the report!" It then converts that field to a string field with six characters. Because the sting is only six characters, the two slash / marks take up two of the characters thus shortening the overall date. So it works fine in 8.5 but not in 9. Interestingly, when I look at the data in Paradox, the date fields display properly. When I look at the table structure via Paradox, it shows the field as being a data field but only 6 characters in length. CR 9 may see that it is a date field but because it is only 6 characters long, CR 9 figures that it really can’t be a date and switches it to a string.

One other test I performed was to run it on a different computer and with a different operating system. Same results. I am at a loss.

 

[Naith]

Have you verified the datasource from 9?

 

[sirius1026]

Hi Naith. Yes. When I verified the datasource, it indicated that the database was up to date. However, because it shows my date fields as strings, it already believes that the database has been restructured. I guess thats what it boils down to. CR 9 thinks that the data base has been restructured when it fact it has not been restructured. It must have something to do how CR 9 sees the data base that is different from how CR 8.5 sees the data base.

 

[Naith]

Can you verify the version and type of driver you are using to connect? The likelihood is that you probably think you're using the same driver but that the version has changed.

Naith

 

[lyanch]

This is the same problem I get with oracel timestamp datatypes and Crystal. I actually convert it to a datetime datatype before I bring it in to crystal. Actually I get a blank 6 char string, but there is too much similarity to ignore. I suspect, along with Naith, that you have indeed changed drivers. You may want to "step back". Unfortunately the newer drivers are not always the best and correct drivers.

Lisa

 

[sirius1026]

The solution. Thanks for everyone's help.

-- xfODBC supports only Crystal Reports 8 and versions of Crystal Reports 9 that have version 9.2.1.512 or greater of
querybuilder.dll and 9.2.3.632 of crw32.dll. (See entry below for information on hot fixes that update these DLLs.) Earlier versions may cause your reports to hang or return invalid data.

-- To use Crystal Reports 9 with xfODBC, you must apply the
following Crystal Reports hot fixes. These hot fixes update
Crystal Reports so that it generates pure SQL92 syntax and prevent
the autolink wizard from creating circular references and
bidirectional links.

-- DBEXDRVR9PLUSWIN_EN.exe (database driver)

-- CR90MAINWIN_EN.zip (Standard and Professional version)