INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Datetime parameter - default times

Datetime parameter - default times

(OP)
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

RE: Datetime parameter - default times

Hi,

Best to use

>= Date and < Date + 1

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Datetime parameter - default times

(OP)
Hi Skip,

My SQL is as follows:

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

RE: Datetime parameter - default times

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Datetime parameter - default times

(OP)
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?

RE: Datetime parameter - default times

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Datetime parameter - default times

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

Thank you

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close