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