crystal reports - verify database changes my date parameters to strings

When I run Verify Database in crystal my date parameters get changed to strings.


I am using crystal version 14.0.0\
My database connection is OLE DB ADO
The provider is  SQL Server Native Client 11.0
My database is SQL Server 2008

The date parameters in the stored poc are defined this way:

    @BegDueDate      date = null,
    @EndDueDate      date = null
ryannAsked:
Who is Participating?

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

x
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.

mlmccCommented:
What happens if you give the dates a default value?

Can you take the = NULL off the declaration?
     @BegDueDate      date,
     @EndDueDate      date 

Open in new window


Why did you do the Verify Database?
Did it warn you of the change?

mlmcc
0
vastoCommented:
Change the stored proc parameters to DateTime, verify the report , then restore stored proc parameters back to Date
0
ryannAuthor Commented:
Is there anyway to know if my connection is SQL Server Native Client 11.0 ?

Attached are the results of the suggestions made. Nothing worked.
Experts-Exchange-attachment.txt
0
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

vastoCommented:
do not verify the report after the stored procedure parameters are returned back to date. Crystal does not understand some sql server types . For example types date or datetime2 will be converted to string because Crystal doesn't know what they mean. If you are looking for temporary solution you can do this:

change sp parameters to datetime
verify
change sp parameters to date and do not verify

this will allow you to run the report and the fields returned by the stored procedure will be OK ( except you have date or datetime2 fields returned)

the other option is to permanently change the stored procedure to datetime. I don't think there is an advantage to use date instead of datetime for the parameters
0
ryannAuthor Commented:
This is a report in progress. From time to time I may need to change the stored proc so will need to verify the database. Once the parm gets changed to string there is no easy way to get it back to type date. I need to have it as a date type.
0
mlmccCommented:
Declare it as a datetime.

Either remember to change it to datetime when you make changes to the SP or set it as datetime and when you are finally done set it back to date.

If you forget to make the change and do a verify, don't save the report.

mlmcc
0

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
James0628Commented:
Like the others said, CR probably just doesn't recognize the date data type for MS SQL.  I don't know if CR was ever updated to recognize that data type after it was added to MS SQL.

 The simplest solution would be to just make the parameters datetime and leave them that way.  If you really need date values (with no time) in the SP, you could always assign the parameters to date variables, and use the variables in your SP.  Depending on what you're doing with the parameters, another option might be to have the SP change the time in the parameter (eg. to 12 AM), so that the time isn't a factor, but using date variables seems simpler.

 Also keep in mind that any date columns in the SP results would also be a problem for CR.  It won't know how to interpret those either.  I'd convert those to datetime.

 James
0
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.