Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

SQL Select by date

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
TheWebGuy38
Asked:
TheWebGuy38
  • 3
  • 3
  • 3
1 Solution
 
Ephraim WangoyaCommented:

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
 
SharathData EngineerCommented:
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
 
Ephraim WangoyaCommented:

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
TheWebGuy38Author Commented:
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
 
Ephraim WangoyaCommented:

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
 
TheWebGuy38Author Commented:
I used the second one, although it is greek to me. it works great! :)
0
 
SharathData EngineerCommented:
Did you try my queries? You don't need extra variables again.
0
 
TheWebGuy38Author Commented:
I didn't understand that Sharath
0
 
SharathData EngineerCommented:
>> 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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