Solved

SQL Select by date

Posted on 2011-03-14
9
659 Views
Last Modified: 2012-06-27
I am trying to do a query by datetime

basically says, select all data by this day.
the problem I am having is that if I put in a day like 2010-03-05
it pulls an empty value because there is a timestamp.

all I want to do is pull data by that entire day.

input is appreciated

query is below
Select * FROM  IncidentReport INNER JOIN
               HowCertain ON IncidentReport.HowCertainID = HowCertain.HowCertainID INNER JOIN
               Locations ON IncidentReport.LocationsID = Locations.LocationsID INNER JOIN
               Perpetrators ON IncidentReport.PerpetratorsID = Perpetrators.PerpetratorsID INNER JOIN
               Tactics ON IncidentReport.TacticsID = Tactics.TacticsID
WHERE (IncidentReport.TimeOccured LIKE @TimeOccured)
ORDER BY IncidentReport.TimeOccured DESC

Open in new window

0
Comment
Question by:TheWebGuy38
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35134205

use between and add time to your upper date limit
declare @Start datetime, @End datetime
set @Start = '03/05/2010'
set @End = '03/05/2010 23:59:59'

Select * FROM  IncidentReport INNER JOIN
               HowCertain ON IncidentReport.HowCertainID = HowCertain.HowCertainID INNER JOIN
               Locations ON IncidentReport.LocationsID = Locations.LocationsID INNER JOIN
               Perpetrators ON IncidentReport.PerpetratorsID = Perpetrators.PerpetratorsID INNER JOIN
               Tactics ON IncidentReport.TacticsID = Tactics.TacticsID
WHERE (IncidentReport.TimeOccured BETWEEN @Start and @End)
ORDER BY IncidentReport.TimeOccured DESC

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35134245
If you are wroking on SQL Server 2008, you can try like this.
SELECT * 
  FROM IncidentReport 
       INNER JOIN HowCertain 
         ON IncidentReport.HowCertainID = HowCertain.HowCertainID 
       INNER JOIN Locations 
         ON IncidentReport.LocationsID = Locations.LocationsID 
       INNER JOIN Perpetrators 
         ON IncidentReport.PerpetratorsID = Perpetrators.PerpetratorsID 
       INNER JOIN Tactics 
         ON IncidentReport.TacticsID = Tactics.TacticsID 
 WHERE CONVERT(DATE, IncidentReport.TimeOccured) = CONVERT(DATE, @TimeOccured) 
 ORDER BY IncidentReport.TimeOccured DESC 

Open in new window

If 2005 or less, you can try this.
SELECT * 
  FROM IncidentReport 
       INNER JOIN HowCertain 
         ON IncidentReport.HowCertainID = HowCertain.HowCertainID 
       INNER JOIN Locations 
         ON IncidentReport.LocationsID = Locations.LocationsID 
       INNER JOIN Perpetrators 
         ON IncidentReport.PerpetratorsID = Perpetrators.PerpetratorsID 
       INNER JOIN Tactics 
         ON IncidentReport.TacticsID = Tactics.TacticsID 
 WHERE DATEADD(dd, 0, DATEDIFF(DD, 0, IncidentReport.TimeOccured)) = 
       DATEADD(dd, 0, DATEDIFF(DD, 0, @TimeOccured)) 
 ORDER BY IncidentReport.TimeOccured DESC 

Open in new window

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35134250

Or if you want to do it more dynamically
declare @Start datetime, @End datetime
set @Start = dateadd(d, 0, datediff(d, 0, @TimeOccured))
set @End = dateadd(d, 1, @Start)

Select * FROM  IncidentReport INNER JOIN
               HowCertain ON IncidentReport.HowCertainID = HowCertain.HowCertainID INNER JOIN
               Locations ON IncidentReport.LocationsID = Locations.LocationsID INNER JOIN
               Perpetrators ON IncidentReport.PerpetratorsID = Perpetrators.PerpetratorsID INNER JOIN
               Tactics ON IncidentReport.TacticsID = Tactics.TacticsID
WHERE (IncidentReport.TimeOccured BETWEEN @Start and @End)
ORDER BY IncidentReport.TimeOccured DESC

Open in new window

0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:TheWebGuy38
ID: 35134261
so like this

although my syntax is wrong here
set @End = @TimeOccured + " 23:59:59"





Create Procedure GetIncidentByDate

@TimeOccured datetime

As

declare @Start datetime, @End datetime
set @Start = @TimeOccured
set @End = @TimeOccured + " 23:59:59"

SELECT *
FROM  IncidentReport INNER JOIN
               HowCertain ON IncidentReport.HowCertainID = HowCertain.HowCertainID INNER JOIN
               Locations ON IncidentReport.LocationsID = Locations.LocationsID INNER JOIN
               Perpetrators ON IncidentReport.PerpetratorsID = Perpetrators.PerpetratorsID INNER JOIN
               Tactics ON IncidentReport.TacticsID = Tactics.TacticsID
WHERE (IncidentReport.TimeOccured BETWEEN @Start and @End)
ORDER BY IncidentReport.TimeOccured DESC

Go

Open in new window

0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 35134283

Do it like this
Create Procedure GetIncidentByDate

@TimeOccured datetime

As

declare @Start datetime, @End datetime
set @Start = dateadd(d, 0, datediff(d, 0, @TimeOccured))
set @End = dateadd(d, 1, @Start)

SELECT *
FROM  IncidentReport INNER JOIN
               HowCertain ON IncidentReport.HowCertainID = HowCertain.HowCertainID INNER JOIN
               Locations ON IncidentReport.LocationsID = Locations.LocationsID INNER JOIN
               Perpetrators ON IncidentReport.PerpetratorsID = Perpetrators.PerpetratorsID INNER JOIN
               Tactics ON IncidentReport.TacticsID = Tactics.TacticsID
WHERE (IncidentReport.TimeOccured BETWEEN @Start and @End)
ORDER BY IncidentReport.TimeOccured DESC

Open in new window

0
 

Author Comment

by:TheWebGuy38
ID: 35134300
I used the second one, although it is greek to me. it works great! :)
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35134321
Did you try my queries? You don't need extra variables again.
0
 

Author Comment

by:TheWebGuy38
ID: 35134483
I didn't understand that Sharath
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35135152
>> I didn't understand that Sharath
You will get the result what you are looking for? Anyways, this question is closed and you got the answer.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question