Crystal Reports Date/Time Issue

[rammy]

I have a stored procedure that gets the date as input parameter. I have connected this stored proc to crystal report. When i run the report, for the date parameter, the crystal report is asking to select time also. But i don't want to choose time, I only need the date component. Is there any way i can supress/remove the time field in crystal.

[Jim Doherty]

I have a stored procedure that gets the date as input parameter. I have connected this stored proc to crystal report. When i run the report, for the date parameter, the crystal report is asking to select time also. But i don't want to choose time, I only need the date component. Is there any way i can supress/remove the time field in crystal.

Can you not in a position to cast or convert your date to varchar as part of the select or where clause?

CONVERT(varchar(10),@YourDateParameter,103)

Regards
Jim

[rammy]

Jim,

I thought about that idea, but crystal will not show the calender when i use varchar as input parameter. The user may have to type the date in the text box and there will be no validation for the date. The calender gives user ablity to select the date they wanted. Is there any other way to hide the time component?

Thanks,
Rammy

[Jim Doherty]

Jim,

I thought about that idea, but crystal will not show the calender when i use varchar as input parameter. The user may have to type the date in the text box and there will be no validation for the date. The calender gives user ablity to select the date they wanted. Is there any other way to hide the time component?

Thanks,
Rammy

Rammy

SQL Server is going to return a date and time together as is its datatype

try this conversion to see how it effects you select cast(convert(varchar, getdate(),101) as datetime) you can amend the 101 to 102 or 103 or whatever your localisation needs demand

I take your point about the Crystal calendar but if you look at the item thread 5896

at this link

[HTML]http://technicalsupport.businessobjects.com/cs/forums/thread/5678.aspx[/HTML]

In addition to that look at this link thread discussion both of which seem to conclude that overall it seems there is no real issue with CR accepting a conversion as I outlined earlier and above

[HTML]http://forums.databasejournal.com/showthread.php?t=35083[/HTML]


Regards

Jim

[harrywhelks]

I agree. The date calendar prompt thinggummyjig is a damned nuisance. And the convert function supplied by a previous post puts the date in mm/dd/yyyy which is US not UK version.
select convert(varchar, getdate(),101)
06/17/2010

I'm sure there is another format code to get it in the UK version but personally I normally select the date in my stored procedure using one of these user-friendly formats listed below to retrieve the date in varchar (i.e. SQL Server equivalent of string )format and then simply get the CR user to type in the date in that format e.g. 2008-08-17 also, make the CR parameter a string also and then you're comparing like with like.

Too bad you can't use the funky date picker calendar - you probably can if you set the input parameter to Date and play around with CR formulas for a bit.

User Friendly Format 1
=====================
Select TOP 15 CAST(DATEPART(yyyy, M.mopendt) as varchar(4))+ '-'
+ RIGHT ('00' + CAST(DATEPART(mm, M.mopendt) as varchar(2)),2) + '-'
+ RIGHT ('00' + CAST(DATEPART(dd, M.mopendt) as varchar(2)),2)
from matter M
1981-12-17
1985-08-21
1998-05-26
1981-09-21


User Friendly Format 2
=======================
Select TOP 15
RIGHT ('00' + CAST(DATEPART(dd, M.mopendt) as varchar(2)),2) + '/'
+ RIGHT ('00' + CAST(DATEPART(mm, M.mopendt) as varchar(2)),2) + '/'
+ CAST(DATEPART(yyyy, M.mopendt) as varchar(4))
from matter M
17/12/1981
21/08/1985
26/05/1998
21/09/1981


User Friendly Format 3 (for display on the CR report)
========================
select top 15
CASE
WHEN datepart(dd,M.mopendt) IN (1, 21, 31) then RIGHT('0' + CAST(datepart(dd,M.mopendt) as varchar(2)),2) + 'st ' + DATENAME(month,M.mopendt)+ ' ' + CAST(datepart(yyyy,M.mopendt) as varchar(4))
WHEN datepart(dd,M.mopendt) IN (2, 22) then RIGHT('0' + CAST(datepart(dd,M.mopendt) as varchar(2)),2) + 'nd ' + DATENAME(month,M.mopendt)+ ' ' + CAST(datepart(yyyy,M.mopendt) as varchar(4))
WHEN datepart(dd,M.mopendt) IN (3, 23) then RIGHT('0' + CAST(datepart(dd,M.mopendt) as varchar(2)),2) + 'rd ' + DATENAME(month,M.mopendt)+ ' ' + CAST(datepart(yyyy,M.mopendt) as varchar(4))
ELSE RIGHT('0' + CAST(datepart(dd,M.mopendt) as varchar(2)),2) + 'th ' + DATENAME(month,M.mopendt)+ ' ' + CAST(datepart(yyyy,M.mopendt) as varchar(4))
END
as friendly_mopendt_ver1 from Matter M
--sample
17th December 1981
21st August 1985
26th May 1998
21st September 1981

[harrywhelks]

Hi,

As an update to the last post, here is how it is done.
No need for user friendly columns although these can be very useful for displaying on the report itself.

You bring in the column from the database (fopen DateTime) and you create a parameter of type Date called DatePicker. Then you click on the filter shaped icon to open the Select Expert and add the below line as a condition.
CDATE({vwLabelsDailyNewFolders.fopen}) = {?DatePicker}

CDATE converts DateTime to Date so you are now comparing like to like and can use the Calendar prompt datepicker supplied by Crystal.


Thanks, Seán