Solved

SQL Select by date

Posted on 2011-03-14
9
653 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
  • 3
  • 3
  • 3
9 Comments
 
LVL 32

Expert Comment

by:ewangoya
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 40

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:ewangoya
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
 

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 32

Accepted Solution

by:
ewangoya 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 40

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 40

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now