Solved

SQL Select by date

Posted on 2011-03-14
9
661 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

696 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