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?
 
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
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.