keezebees
asked on
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.vwForReportMetParamete
(@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
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.vwForReportMetParamete
(@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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Tought I had split the points but gave them all to non_zero, sorry, don't know how to correct.
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.vwForReportMetParamete
(@StartDate datetime,
@StopDate datetime)
AS
begin
set @StopDate = @StopDate -1 ----------------- here is solution ------------------
SELECT * FROM dbo.vwForReport
WHERE StartTimeStamp >= @StartDate
AND StartTimeStamp <= @StopDate
end
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.vwForReportMetParamete
(@StartDate datetime,
@StopDate datetime)
AS
begin
set @StopDate = @StopDate -1 ----------------- here is solution ------------------
SELECT * FROM dbo.vwForReport
WHERE StartTimeStamp >= @StartDate
AND StartTimeStamp <= @StopDate
end
ASKER
why is it not such a good idea to use functions in where?
the query must scan whole table and evaluate function on each row. You can see it in explain.
ALTER PROCEDURE dbo.vwForReportMetParamete
(@StartDate datetime,
@StopDate datetime)
AS SELECT dbo.vwForReport.*
FROM dbo.vwForReport
WHERE CONVERT(datetime, FLOOR(CONVERT(float(24), StartTimeStamp)) ) between @StartDate and @StopDate