Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Datetime parameter - default times 1

Status
Not open for further replies.

jf111

Programmer
Apr 4, 2016
15
GB
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
 
Hi,

Best to use

>= Date and < Date + 1

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

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

My SQL is as follows:

SQL:
 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.
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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?
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Yes I can! That worked perfectly, I don't know why I didn't think of it before.

Thank you
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top