on 06-21-2010 8:05 PM
Hi, I just moved to SQL Server 2008 and one of my fields is a 'date' type. The data shows as "YYYY_MM_DD". When I add this field to my Crystal Report it does not show up as a date. I am using Crystal Reports XI R2. I tried to use the Date function with it but it gives me an error "Bad Date Format String". Is this a bug in Crystal? What is the best way for me to get my date formatted in mm/dd/yyyy?
Thanks,
I found a fix for this problem! I had the same issue and we found that it's the global settings of the computer operating system, not a setting in Crystal or a problem with SQL.
Go to:
-Start > Control Panel > Region and Language > Additional Settings
-Change "No. of digits after decimal" to 0 (zero)
-Change "digit grouping" to 123456789 (no comma)
-Click "ok" for both windows and close
Crystal Reports 2008 v 12.0.0.683
Product Type: Full
Sandra
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Don and Stacie.
I am facing an interesting variation of this issue and have posted my own question on this topic. If this issue has been resolved or if either of you have any insight to share with me, it would be very much appreciated!
Thanks so much,
Matt
Edited by: Matt Carrithers on Aug 12, 2010 9:05 PM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks so much Don.
I compared the dll from the above post to the crdb_ado.dll that I have in my existing Crystal Reports XI R2 installation. The date, size and version for both matched.
On the off chance that I was missing something, I saved off my original dll and copied new one into the same location. I then experimented with the reports and found that I was experiencing the same issues.
Could it be that the wrong dll was posted? Perhaps Fix Pack 6.2 contains more than this dll?
I really appreciate your help with this.
Thanks again,
Matt
This issue had me a little curious so I decided to do a little test.
The test report was built using the following:
SQL Server 2008 R2
OLE DB connection w/ Native Client 10 driver
Crystal Reports XI R2 SP3 (or 4???) 11.5.10.1263
I didn't have any problems with either of the new date formats (Date & SmallDateTime)
The test report includes all 3 date types plus 2 formulas that use the "Date" type.
I noticed that the "fDate" field is listed as a String[10] field, but CR didn't seem to have a problem converting it to a usable date with either of the following formulas
Date({Command.fDate})
or
Date(REPLACE({Command.fDate},"-",","))
Feel free to take a look...[NewDateTypes.rpt|http://docs.google.com/leaf?id=0B_0KY03Gs2knYWZlMDI5NjMtZmNmNy00ODE2LTkzOTktMzY3MzhhMTBhM2Y2&sort=name&layout=list&num=50]
All of the dates shown were left in their defaulted format.
HTH,
Jason
Wow Jason, you really went above and beyond in this one. Thanks for your investigation.
This issue I am facing is exactly what you experienced with a date field being seen by Crystal as a string. In your example, you accommodated it by converting to converting it to a usable date field.
I actually explored doing this, figuring that it could be a workaround. When I originally convert over to SQL2008 and save off the report, the date type fields are seen as date types and there are no issues in my formulas.
At this point, I am unable to modify my formulas to convert the date type database fields to a date. Crystal scratches its head and quixotically says, "A date-time is required here" when I try to save my formula. As a test, I substituted a date string in the formula for the date type database field and no errors were found. I think Crystal chokes when it is told to convert a date type field into a date type field.
But, if I save off the report and close my Crystal Reports XI R2, then open it up again and open the report, Crystal will see what was originally a date type field as a string. At this point, I can modify my formulas with date() or datevalue() and things work.
My concern with this workaround is that it does not address the original issue of a date type being seen as a string. Also, if I implement this workaround and then this issue is fixed somehow, I would be concerned that my reports would suddenly fail when Crystal tries to convert a date type field to a date type field.
I would really like to find a way to get Crystal Reports XI R2 to be able to consistently see date type fields as date type fields. My suspicion is that when I close Crystal Reports XI R2 and then reopen it, it "forgets" the SQL Server Native Client 10 driver and defaults to an earlier SQL driver that does not support the date type fields.
Thanks again for your investigation into this. I really appreciate both your and Don's time in looking into this.
If you have any other thoughts on this, I would very much appreciate them!
Matt
I'm have versions XI R2 SP6 installed.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Where do I fiind the Fix Pack 6.2? I didn't see it on the Crystal downloads page.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Stacie,
Are you using the Native 10 client driver or? If so Ms changed the name of the driver and CR doesn't recognize it any more.
Also, apply Service Pack 6 and then Fix Pack 6.2 and try again.
If that doesn't resolve it then try a different driver or go into the default report properties and change the default date format. You may want to also try using the Convert Null to Default option.
Thank you
Don
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Stacie,
Sorry, it's not available for non-support contract users.... long story...
Not sure what version you are on but start off by un-installing CR and then using this full build version:
https://smpdl.sap-ag.de/~sapidp/012002523100011802732008E/crxir2_sp4_full_build.exe
Go into the License Manager first and copy the keycode if you don't have a hardcopy somewhere. Then install SP4.
SP6 incremental is here: https://smpdl.sap-ag.de/~sapidp/012002523100015859952009E/crxir2win_sp6.exe
See if that works if not then let me know and I'll send 6.2 through our FTP server.
Thanks again
Don
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.