Datetime parameter - default times 1

[jf111]

Hi,

Is it possible to set a datetime parameter to always use a particular time?

I have two parameters used in a SQL query which are datetime. It defaults to the current system time of the user selecting the datetime which can cause data to be missing from the report.

I would like {?Start} to always be 00:00:00 and {?End} to always be 23:59:59. I have tried using a 'Date' parameter but it causes errors with my SQL.

Thank you

 

[SkipVought]

Hi,

Best to use

>= Date and < Date + 1

What's your SQL that's causing an error?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jf111]

Hi Skip,

My SQL is as follows:

 select 
 CONVERT(varchar(11),[6007EventTime]) as Date
 ,[6000UserName]
 ,min([6007EventTime]) as EntryTime
 ,max([6007EventTime]) as ExitTime
 ,departmentname
,CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS, min([6007EventTime]), max([6007EventTime])), 0), 114) AS TimeDiff
,CAST((max([6007EventTime])-min([6007EventTime])) as datetime) AS TimeDiff2
from vw_reportquery rq
join vw_ViewUserDetails ud on ud.UserID = rq.UserID
where [6007EventTime] between {?Start} and {?End}
group by CONVERT(varchar(11),[6007EventTime]),departmentname,[6000UserName]
order by departmentname

If I try to pass Date parameters to this query it gives conversion errors.

This works absolutely fine except that I can't rely on users to set the times to 00:00:00 and 23:59:59 by themselves! They will forget so I'd like to automatically set this and keep the date they chose.

 

[SkipVought]

Well here's what is puzzling to me.

Every single time value will be between 0:00: and 23:59:59, so what's the point of the criteria?

If the table contains data for multiple dates, then the Criteria includes all times for all dates, so what's the point of the criteria?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jf111]

I can see why you would think that

The criteria is to ensure that results always include records from the very start of the {?Start} date and to the very end of the {?End} date.

For instance if I run the report at 10:05:33 and select the following criteria:

{?Start} = 01/01/2016 10:05:33
{?End} = 07/01/2016 10:05:33

I will miss out 10 hours of records in the morning of each date. I would like it to always choose 00:00:00 and 23:59:59 but keep the date. Crystal appends the current time unless you manually change it

Does that make sense?

 

[SkipVought]

so you're saying that the system adds the current time to the date parameter? Wierd!

So can you truncate 607EventTime to a Date only, as you have done in the Select clause, in the Where clause?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jf111]

Yes I can! That worked perfectly, I don't know why I didn't think of it before.

Thank you