Solved

SQL Select by date

Posted on 2011-03-14
9
654 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
data lookup in Oracle - need suggestions 55 102
CROSS APPLY 4 44
Determine log file requirements 7 34
ISDATE() not working properly on my table? Any suggestions. 7 0
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 …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

911 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

25 Experts available now in Live!

Get 1:1 Help Now