Start Date and Start Time

[Crystalguru]

I am having trouble with the Start Date and Start Time parameter. Guess I just can’t get my head into it this morning.

My report has two parameters:
Start Date
Start Time



If the user enters the following:
Start Date: 02/07/06
Start Time: 14:00


The Report Period will be 24 hours:
02/07/06 14:00 – 02/08/06 14:00

Here’s my problem:
Start Date and Start Time are separate fields.


I have to convert CV3Order.RequestedDTM into Date format and Time format.

This is what I get:
Convert(varchar,CV3Order.RequestedDTM,101) = 02/07/06
Convert(char(5),CV3Order.RequestedDTM, 108) = 14:28

When I do the comparison in SQL:

(CONVERT(VARCHAR,CV3Order.RequestedDTM,101) between convert(varchar,@startdate,101) and convert(varchar,dateadd(hh,24,@startdate),101)

AND Convert(char(5),CV3Order.RequestedDTM,108) between convert(char(5),@starttime,108) and convert(char(5),dateadd(hh,24,@starttime),108)



I get these results:
02/07/06 between 02/07/06 and 02/08/06
And
14:28 between 14:00 and 14:00

No rows returned due to the Start Time. Although it’s programmed for 24 hours, I don’t think it’s “seeing” it that way.



Any ideas?

 

[vongrunt]

Data type of @startdate and @starttime is... ?


------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[vongrunt]

Based on buncha CONVERT()'s above I guess @startdate is datetime, and @starttime is either string or datetime. And CV3Order.RequestedDTM is datetime. In that case...

CV3Order.RequestedDTM between @startdate+@starttime and 1+@startdate+@starttime

Voila.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Crystalguru]

Thanks, works like a charm.