Want to select data only on day not by time.

Hy there,

this should not be that hard.

I have a datetime column on wich I want to select data between 2 dates
Here is the code.
------------------------------------
ALTER PROCEDURE dbo.vwForReportMetParameters
(@StartDate datetime,
@StopDate datetime)
AS SELECT     dbo.vwForReport.*
FROM         dbo.vwForReport
WHERE     StartTimeStamp >= @StartDate AND StartTimeStamp <=  @StopDate
-------------------------------------------------------------------------------------
When I choose between 1/1/2005 and 1/1/2005 I want it to show all the data from 1/1/2005.
this will not happen because the = operator in 'StartTimeStamp <= @StopDate' is also looking at the timepart of the StartTimeStamp.

How do I go about leaving the time out of my selection?

thnx.

Kees
keezebeesAsked:
Who is Participating?
 
Melih SARICAOwnerCommented:
ALTER PROCEDURE dbo.vwForReportMetParameters
(@StartDate datetime,
@StopDate datetime)
AS SELECT     dbo.vwForReport.*
FROM         dbo.vwForReport
WHERE     cast(convert(varchar(10),StartTimeStamp,104) as datetime) between @StartDate and @StopDate

0
 
mikelittlewoodCommented:
personally I would use

ALTER PROCEDURE dbo.vwForReportMetParameters
(@StartDate datetime,
@StopDate datetime)
AS SELECT     dbo.vwForReport.*
FROM         dbo.vwForReport
WHERE     CONVERT(datetime, FLOOR(CONVERT(float(24), StartTimeStamp)) ) between @StartDate and @StopDate
0
 
keezebeesAuthor Commented:
Well, what can I say,

Both solution work fine So I think I will split points.
Just wondering about MikeLittlewood's solution. What happens here? DateTime to float and then to date, what does Floor do?????

Anyway, thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
keezebeesAuthor Commented:
Tought I had split the points but gave them all to non_zero, sorry, don't know how to correct.
0
 
racekCommented:
it is not so good idea to use functions in where.
If can get more efficient solution when you accept, that set @StopDate = @StopDate -1 is exact what you need for max time:

ALTER PROCEDURE dbo.vwForReportMetParameters
(@StartDate datetime,
@StopDate datetime)
AS
begin
set @StopDate = @StopDate -1 ----------------- here is solution ------------------
SELECT  *   FROM   dbo.vwForReport
    WHERE  StartTimeStamp >= @StartDate
         AND StartTimeStamp <=  @StopDate
end
0
 
keezebeesAuthor Commented:
why is it not such a good idea to use functions in where?
0
 
racekCommented:
the query must scan whole table and evaluate function on each row. You can see it in explain.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.