How to Change DateTime parameter to Date for stored procedure in Crystal Reports

I have a CR .rpt file that uses a SQL stored procedure passing 2 datetime parameters and other stuff. The parameters are really dates, but that type was not available when the report was written. I am now moving to Crystal 2011 and MS SQL 2012 which handle the date type. I have redone the SP to expect date types. I am not able to change the parameter types in the report definition from datetime to date types. How do I do this?

Old: Crystal XI & SQL 2005
Target: Crystal 2011 & SQL 2012
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What have you tried?

DId you try a VERIFY DATABASE to get Crystal to read the new stored procedure?

JoeM21Author Commented:
I have tried verify database and Set Datasource Location. They seem happy even though Crystal thinks the parameters are still datetime and SQL has them as date.

Note that the stored procedure and database name are not changed. The data is in a new SQL instance on a new server.
Create a new report in the newer version of CR and connect it to that stored procedure, and see if the parameters are date or datetime.  If they still show up as datetime in CR, then at least you know that it's not an issue with the old report.

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

JoeM21Author Commented:
I used CR Designer 2011 (Version RTM) to start a blank report based on the old SP. After I logged in to the database, it asked me to enter values showing a calendar icon to the right of each. I left them as null. I then got rparameter values of type DateTime.
JoeM21Author Commented:
Last comment got chopped. Recreating ...
JoeM21Author Commented:

I made some changes to run your test. The old sp is on a database in ServerOLD\InstanceSAME (SQL 2005) with DateTime parameters @BeginningDate and @EndingDate. I modified the SP in a copy of the database on ServerNEW\InstanceSAME (SQL 2012) to use Date parameters @BeginningDateNEW and @EndingDateNEW.

I used CR Designer 2011 (Version RTM) to start a blank report based on the old SP. After I logged in to the database, it asked me to enter values showing the old parameter names with a calendar icon to the right of entry window. I left them as null. I then got parameter values of type DateTime.

I used CR Designer 2011 (Version RTM) to start a blank report based on the new SP. After I logged in to the database, it asked me to enter values showing the new parameter names with not calendar icon to the right of each. I left them as null. I then got parameter values of type String.

Whatever is going wrong does not seem to involve the report definition.

Note that the database copy on the new server is not an exact copy of the other database. It has been modified in conjunction with an upgrade of the underlying application. The tables involves with the SP were not modified.
How are you connecting to the db?  ODBC?  OLE DB?

 I did a little Web searching and a couple people with similar problems said they were fixed by changing the connection (ODBC or OLE) from "SQL Server" to something like "SQL Server Native Client 10".  I guess the older connection didn't know how to handle the date data type.

JoeM21Author Commented:
I think that is part of the problem. My original connection is through an ODBC datasource using the Microsoft SQL Server driver version 6.01.7601.17514 (released prior to SQL 2012). I have changed the new datasource to use the Microsoft SQL Server Native Client 11.0 version 2011.110.2100.60. That did not help the original problem: The switch to the new source still gave parameters of the wrong type (String).

However if I start a new report using the newer driver, Crystal now sees the SP parameters correctly as type Data.

I have seen comments that CRW sometimes gets confused when changing to a different SP with the same parameter names. Tomorrow I will go over my test changing location between the two datasources to see if I really set it up correctly.
At least the new report seeing them correctly shows some progress.

 Maybe make sure the old report is using the new connection and do a "verify database".

 And if you think CR might be confused about the old parameters, maybe try changing the parameter names in the SP temporarily, or even add some new parameters with different names (so you've got the old parameters and the new ones).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JoeM21Author Commented:
Thanks for your help and your pointing me in the right direction. I think I have finally got it working. At least for the first report. The problem appears to be a combination of a bad (outdated?) driver for SQL Server 2012 and CRW Designer's caching problems.

I left the old data source pointing to the old SQL instance with the old SQL Server Driver. I created the new datasource pointing to the new SQL instance with the new SQL Server native client 11. I made sure that the CRW Designer was NOT logged on to any server. I then brought up the existing report definition, verified it on the old datasource, and set the location to the new datasource.  This worked smoothly.

If I make multiple such passes without closing the connections each time, the Designer appears to assume some information based on the previous attempts. In my attempts to go from DateTime parameters to Date parameters it either did not ask for the parameters for the new source (and assumed they were still DateTime) or asked for them and then set the type to String. This happened even if I changed the parameter names in the new stored procedure. I am guessing that this is a Designer caching problem because I can avoid it by closing all the connections each time.
Interesting.  Thanks for posting those details.  And I'm glad I could help.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.