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

Greetings qcheng,

I'm so sure I wrote a reply to this post yesterday, but I guess I must have closed the window before Posting it, since I don't see it online today.

Anyways, many thanks for taking the time out to send me your explanations along with examples to my problem. I did toy with the idea of using a text field earlier in my stored procedure parameter and having the same prompted to the user to receive a date input, but I had decided against it because a text field would not display a date picker control (that comes with the date and datetime fields) and this could introduce user errors.

Besides this, my report is country relevant, so with a text control, my users in the East might enter a date in the dd/mm/yyyy format while in the West it maybe entered in a mm/dd/yyyy format. I would have no way of knowing what they entered was correct or not.

I was actually looking for someway to:

-1- Either hide the time part of the datetime control in Crystal Reports, or

-2- Specify a date in the stored procedure rather than a datetime so that crystal reports links and prompts the user for a date without time.

I already have a small function in my stored procedure which sets the time portion of the datetime variable to "00:00:00" once it returns from Crystal Reports, so I never have a problem about the time no matter what the user enters.

However, I am begining to believe that the entire issue is a limitation of Crystal Reports / SQL Server. I guess I cannot have my cake and eat it too

Your solution is definitely a good one and I will move in that direction in case management decide that they want to go with a text control rather than a datetime control. In such a case I will prompt the user with an appropriate message "Enter a date in yyyy/mm/dd format" or such.

Thanks once again, and do post any solution to this if you should find one in the future.

~X

[Former Member]

Post Author: qcheng

CA Forum: General

Hi Esc2xtc,

The answer to your question #1 is yes.

As to #2,

create table testxxx(field1 int,field2 datetime)insert into testxxxvalues(20,convert(datetime,'01/01/2007'))insert into testxxxvalues(30,convert(datetime,'01/02/2007'))go

select * from testxxxgo

ALTER PROCEDURE dbo.testyyy(@Param nvarchar(50))AS SELECT field1, field2, dbo.BO_CrystalDateFormat(field2) AS CrystalDateFROM dbo.testxxxWHERE (dbo.BO_CrystalDateFormat(field2) = @Param)GO

Now you can use the stored procedure dbo.testyyy as your crystal report data source.

Hope this is what you want.

Thanks

[Former Member]

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?

[Former Member]

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

[Former Member]

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.

[Former Member]

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 &#91;dbo&#93;.&#91;sp_TestX&#93; (@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?

[Former Member]

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.

[Former Member]

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)

[Former Member]

Post Author: Dipesh

CA Forum: General

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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)

AS

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

[Former Member]

Post Author: Esc2Xtc

CA Forum: General

Hi guys,

&#91;Hi Archmage&#93; 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. &#91;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...&#93;

[Former Member]

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 !

~X

[Former Member]

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.

[Former Member]

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.

[Former Member]

Post Author: yangster

CA Forum: General

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

[Former Member]

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

[Former Member]

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.