[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

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
0
keezebees
Asked:
keezebees
1 Solution
 
Melih SARICACommented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now