Datetime To Date Parameter issue...

[Former Member]

Post Author: ram323

CA Forum: General

How can I convert the parameter from Datetime data type to Only Date Parameter so that everytime the parameter prompt window pops-up, the user will only input the Date and not seeing the Time on the parameter prompt window..I am using Crystal Reports XI..and my stored procedure uses DateTime data type....Need Help!!! a step by step procedure will be greatly appreciated.....Thanks much....

[Former Member]

Post Author: Esc2Xtc

CA Forum: General

Hi guys,

[Hi Archmage] With respect to your reply, I think it should work perfectly well if we were trying to take care of the case AFTER getting a value from crystal reports and within SQL Server.

But what about in the case where we do not want to show the time part to the user at all thus cutting the problem from the root?

Management here is used to seeing only date parameters being asked from crystal reports not datetime (since prior to this all reports were linked to tables and views where this problem does not arise if we have "user-defined parameter", but since this is a stored procedure, crystal reports HARDCODES the parameters into the reports and refuses the developer from editing it. Its almost like a non editable system defined parameter.)

SQL server has smalldatetime and datetime datatypes. Now both of these come with a time part attached to it. It does not have any date datatype without the time part like crystal reports has. And since the stored procedure paramaters do not allow editing, we are not able to change datetime to date only. Unfortunately this seems to be the basis of the issue coming up.

Below is a good explanation of the problem. I came across this post on one of the other sites while I was looking for an answer to the same problem. Any help will greatly be appreciated. [I have a report written in CR10 which uses a stored procedure as the datasource. The stored procedure has 3 input parameters, two of which are datetime data types. When the crystal report is run, it prompts the user for the 3 parameters & runs just fine except...For the date parameters, it prompts the user to enter a time as well. In previous versions of Crystal, I never experienced this. Parameters that were datetime data types in the stored procedure were just prompted for the date when the calling crystal report was run.

I attempted to edit the parameters in the report, but the "Value Type" is grayed out (I assume since the parameter is associated with the stored procedure, rather than being a user defined parameter in the report). These date parameters are used in a BETWEEN statement within the procedure, so if the user just enters a date but accepts the default time, the returned data gets flakey due to the addition of the time value passed by the parameter.

I tried setting a default value for the time, but I have to enter a date along with the time, which the report then uses as the default. The user then has to uncheck the "Pick from defaults" checkbox in order to enter their own date. This is really unwieldly for the end user, especially when they were previously able to just enter a date and not have to be concerned with a time at all.

Does anyone have suggestions on working around this issue?

Thanks in advance...]