Question Re: Datetime To Date Parameter issue... in Technology Q&A

Datetime To Date Parameter issue...

Former Member — Sun, 15 Jul 2007 05:21:59 GMT

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

Re: Datetime To Date Parameter issue...

Former Member — Mon, 16 Jul 2007 15:38:58 GMT

Post Author: V361

CA Forum: General

Create your param as a date only, and add the time in your select formula

{Field.D_Time} in {?date_only} + time(00,00,00) to {?date_only} + time(23,59,59)

This will give you the entire day, between the hours specified, for the Field.D_Time

This should work just fine.

Re: Datetime To Date Parameter issue...

Former Member — Mon, 16 Jul 2007 23:48:10 GMT

Post Author: ram323

CA Forum: General

It will not work because you already pass the parameter prompt window on your formula...I'm using a datetime parameter on my stored procedure and I need to ONLY see the Date on the parameter prompt window...this is of course...before you see the report....any advise??....

Re: Datetime To Date Parameter issue...

Former Member — Tue, 17 Jul 2007 05:56:16 GMT

Post Author: yangster

CA Forum: General

Alter your stored procedure to take in a date prompt. What kind of db are you using?

Re: Datetime To Date Parameter issue...

Former Member — Tue, 17 Jul 2007 12:08:43 GMT

Post Author: qcheng

CA Forum: General

I am using Crystal Report XI and data source is MS SQL server. I also had this problem and could not find a solution from Crystal Report side. What I did is to create a scalar-valued function in SQL server to convert the datetime field to Crystal parameter date format.

Re: Datetime To Date Parameter issue...

Former Member — Wed, 18 Jul 2007 20:08:36 GMT

Post Author: Archmage

CA Forum: General

You shouldn't be having this issue as I also have CR11 and work with MS SQL05. However,I did remember having this issue with an old report several years ago and the solution can be handled on the stored procedure side.

Simple example:

create proc XXXX (@date datetime) as

set @date=cast(convert(varchar, @date,101) as datetime) --- drop time portion

select * from Table.A where date=@date

hope that helps.

Re: Datetime To Date Parameter issue...

Former Member — Fri, 26 Oct 2007 00:09:57 GMT

Post Author: Esc2Xtc

CA Forum: General

Hi Qingping,

In response to a query with the above subject, you mentioned that you created a scalar-valued function in SQL Server to convert the datetime field to crystal parameter date.

Can you please clarify how you did this? I am facing the same problem and am begining to believe that this is a Crystal Report limitation since no amount of research gives me a solution. Thanks !

Re: Datetime To Date Parameter issue...

Former Member — Fri, 26 Oct 2007 00:30:03 GMT

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

Re: Datetime To Date Parameter issue...

Former Member — Sun, 28 Oct 2007 23:50:11 GMT

Post Author: qcheng

CA Forum: General

I created a Crystal Report date format user definited function in SQL 2000 then applied this function to the datetime field to generate a new field called "CrystalDate" in the stored procedure. You might try the following steps:

1) In SQL 2000 Enterprise Manager, right click User Defined Functions and select 'New User Defined Function...'. Copy paste the following code in TEXT editor area and click OK to save it.

ALTER FUNCTION dbo.BO_CrystalDateFormat

(@sqlDatetime datetime)

RETURNS char(10)

begin

declare @CrystalDate char(10)

select @CrystalDate = convert(varchar, year(@sqlDatetime))+

case when month(@sqlDatetime)<10 then '-0'+convert(varchar,month(@sqlDatetime))

else '-'+convert(varchar,month(@sqlDatetime)) end

+ case when day(@sqlDatetime)<10 then '-0'+convert(varchar,day(@sqlDatetime))

else '-'+convert(varchar,day(@sqlDatetime)) end

return @CrystalDate

end

2) In the stored procedure, apply this function (as other SQL 2000 functions like getdate(), Year() etc) to the datetime field with the parameter to create a new filed and name it.

3) Move the datetime parameter to this new filed.

Hope this helps

Thanks

Re: Datetime To Date Parameter issue...

Former Member — Mon, 29 Oct 2007 15:12:12 GMT

Post Author: V361

CA Forum: General

Back to the original question,.... if you use this

{Field.D_Time} in {?date_only} + time(00,00,00) to {?date_only} + time(23,59,59)

you must change the parameter to Date, then it should work fine.

Re: Datetime To Date Parameter issue...

Former Member — Mon, 29 Oct 2007 16:46:24 GMT

Post Author: sleahcim

CA Forum: General

I agree with V361. Select "Date" for the Parameter type, and it should not display a DateTime.

Also, you can modify Field formats under File --> Options --> Fields --> DateTime. Format your DateTime fields to be dates.

-Michael

Re: Datetime To Date Parameter issue...

Former Member — Mon, 29 Oct 2007 18:11:49 GMT

Post Author: Dipesh

CA Forum: General

You guys seem to have a handle on dates and could really do with some help.

Hope you don't mind me asking but I'd really appreciate it if you could have a look at the following thread.

http://technicalsupport.businessobjects.com/cs/forums/post/11892.aspx

Re: Datetime To Date Parameter issue...

Former Member — Mon, 29 Oct 2007 22:55:18 GMT

Post Author: qcheng

CA Forum: General

Hi Dipesh,

You may use the method I mentioned above to add a new field to your LOV. When you create your dynamic paramter with datetime format, take the original datetime field as your value, the new field as description and set option 'Prompt With Description Only' as true. When you were prompted the datetime parameter, you will see the description only.

I think this is what you want.

Thanks

Re: Datetime To Date Parameter issue...

Former Member — Tue, 30 Oct 2007 12:36:14 GMT

Post Author: Dipesh

CA Forum: General

Not to sure where to set 'Prompt With Description Only' as true

Re: Datetime To Date Parameter issue...

Former Member — Tue, 30 Oct 2007 14:38:34 GMT

Post Author: V361

CA Forum: General

Dipesh,

set 'Prompt With Description Only' as true

in the parameter area, it should be under the Value Options section.

(This may be CR XI specific)

Re: Datetime To Date Parameter issue...

Former Member — Wed, 31 Oct 2007 00:47:06 GMT

Post Author: Esc2Xtc

CA Forum: General

Hi qcheng,

Many thanks for taking the time out and sending in your solution.

Unfortunately this might not help me.

Correct me if I am wrong, but what your solution seems to be doing is converting the datetime parameter to a date value AFTER it has been entered by the user and after crystal reports sends it to SQL.

Rather what I need is some method to display ONLY A DATE prompt to the user in crystal reports without the time part. Management does not want the user to see the time field at all in crystal reports when entering a date.

Is this possible at all when linking crystal reports to SQL stored procedures with datetime parameter field?

Thanks.

Re: Datetime To Date Parameter issue...

Former Member — Wed, 31 Oct 2007 00:55:32 GMT

Post Author: Esc2Xtc

CA Forum: General

Hi v361,

Many thanks for your reply.

However, this solution does not work for a crystal report that is linked to a stored procedure that has a datetime parameter. When creating a report, while linking it to a stored procedure from which you can extract data, the report DISABLES the datatype (along with many other properties) of this parameter field. This results in the developer not being able to change it to a date but having to stick with this datetime prompt.

Here is an example for you to try (replace the <table> and <Date> below with your respective values:

ALTER PROCEDURE [dbo].[sp_TestX] (@param_date smalldatetime)ASBEGINSELECT * FROM <table> WHERE <Date> = @param_dateEND GO

Now create a new report linked to this stored procedure. Crystal Reports will hardcode the @param_date paramete into your report without waiting for you to do it. Try to edit the datatype of this field? I work on Crystal Reports Version 10. Let me know if you are able to do this?

Re: Datetime To Date Parameter issue...

Former Member — Wed, 31 Oct 2007 01:02:49 GMT

Post Author: Esc2Xtc

CA Forum: General

Hi Michael,

Thanks for your response to my query.

#1 As you can see from my previous post, I cannot select "Date". This has been disables by Crystal Reports. Is there any way to re-enable it?

#2 I tried what you specified. Here are the steps I tried

[To modify Field formats under File --> Options --> Fields --> Date and Time button --> Date and Time tab --> Customize button -->

Set Order = Date --> Select OK to Save.]

Date Time is still displayed in parameter selection than date.

However even if this works, it might not be a feasible solution since I would need to go around to each users desk changing this setting?

Would appreciate any other advice you could send me. Thanks.

Re: Datetime To Date Parameter issue...

Former Member — Wed, 31 Oct 2007 02:05:50 GMT

Post Author: qcheng

CA Forum: General

Hi Esc2Xtc,

I meant that you need to edit your stored procedure using the method I mentioned. The converting the datetime field to a text field for parameter display purpose only should have been complated in SQL server BEFORE your Crystal report linked to the stored procedure. Your parameter field in the stored procedure is the converted text field, rather than the datetime field.

Thanks

Re: Datetime To Date Parameter issue...

Former Member — Wed, 31 Oct 2007 23:15:24 GMT

Post Author: Esc2Xtc

CA Forum: General

Hi Qcheng,

Some queries I was hoping you could answer,

#1. Would your last reply mean that my crystal report will have a string (text) field parameter for date information data entry rather than a datetime type (or date type) field?

#2. Also in your reply with the code snippet, could you give me an example of step 2 and 3?