cancel
Showing results for 
Search instead for 
Did you mean: 

Problem with SQL date type field

Former Member
0 Kudos

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,

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

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

Former Member
0 Kudos

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

0 Kudos

download crdb_ado.zip I attached at this time: Posted: Jun 25, 2010 9:59 AM

Then it should work.

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

It looks like that I can't award points under this thread because it is not my original question.

If you would like to move your future comments or answers to [my own question thread|;, I think I should be able to award them.

Thanks again everyone for your time and effort in looking into this!

Matt

Former Member
0 Kudos

I'm have versions XI R2 SP6 installed.

0 Kudos

Hi,

The patch is too big to send through our FTP server, 250 meg limit. So I'll try attaching just crdb_ado.dll to this post....

Rename your original and copy this one in it's place.

Thank you

Don

Former Member
0 Kudos

Where do I fiind the Fix Pack 6.2? I didn't see it on the Crystal downloads page.

0 Kudos

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

Former Member
0 Kudos

I'm using OLEDB for my connection.

0 Kudos

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