Query in SQL Server 2005 Where in 2 column

Question

Good day!! to all

im kinda stuck in here...

here is the senario

i have a table violation

with column of

ID (primary key)

DateCommitted (Date Time)

TimeCommitted (Date Time)

ViolationComitted (varchar(250))

im not the one who created the table im kinda maintaining the system

and the user wants to have a search type where they can search by date + time
eg. From: 01-01-2014 5:30PM to 02-10-2014 11:00AM

im kinda confuse how can i combine the two table in the where clause
where i can search with the date and time combine to get the proper result.
something like this.

SELECT * FROM Violation where ( DateCommitted +TimeCommitted Between '01-01-2014 5:30PM' AND '02-10-2014 11:00AM' )

hope for your help guys.. thanks in advance!!

Answer 1

Can you post some sample rows from that table, so that we know what you are talking about?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Mr.Cabral,

Check if this helps:

create table #temp
(
	id int,
	datecommitted datetime,
	timecommitted datetime,
	violation varchar(255)
)

insert #temp select 1,'2012-09-23','11:45','violation1'
insert #temp select 2,'2012-09-29','10:45','violation2'
insert #temp select 3,'2012-10-19','14:45','violation3'

select id,violation from 
(
select id,cast((left(datecommitted,12)+' '+right(timecommitted,7)) as datetime) as datetime_committed,violation 
from #temp
) tt
where datetime_committed between '09-23-2012 11:00 AM' and '09-30-2012 11:00 PM'

---

Thanks,
Jay
<If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

Answer 3

Try this

CREATE TABLE Voilation
(
  ID INT,
  DateCommitted datetime,
  TimeCommitted datetime,
  ViolationComitted varchar(250)
 )
INSERT INTO Voilation VALUES
(1,'01-01-2014','5:30','testing1'),
(1,'01-01-2014','12:30','testing2'),
(1,'01-01-2014','14:30','testing3'),
(1,'01-04-2014','5:50','testing4'),
(1,'01-02-2014','9:10','testing5'),
(1,'05-04-2014','5:50','testing6'),
(1,'01-04-2014','23:40','testing7'),
(1,'01-04-2014','02:01','testing8')


SELECT *
FROM Voilation
WHERE (CAST(DateCommitted AS DATETIME) +
 CAST(TimeCommitted AS DATETIME) )
BETWEEN 
'01-01-2014 5:30PM' AND '02-10-2014 11:00AM'

Working example: http://sqlfiddle.com/#!3/549ad/6

Hope this will help

Answer 4

try this

create table #temp
(
	id int,
	datecommitted datetime,
	timecommitted datetime,
	violation varchar(255)
)

insert #temp select 1,'2014-01-23','11:45','violation1'
insert #temp select 2,'2014-01-29','10:45','violation2'
insert #temp select 3,'2014-01-19','14:45','violation3'

SELECT *
FROM #temp
WHERE CONVERT(DATETIME, CONVERT(VARCHAR(10), Datecommitted, 120)+' '+CONVERT(VARCHAR(12),CONVERT(time, timecommitted))) 
BETWEEN '01-01-2014 5:30PM' AND '02-10-2014 11:00AM' 

DROP TABLE #temp

Please mark as answer, if this has helped you solve the issue..

Good Luck :) , visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Answer 5

SELECT * FROM Violation where DATEADD(dd,DATEDIFF(dd,0,DateCommitted),TimeCommitted) BETWEEN '01-01-2014 5:30PM' 
AND '02-10-2014 11:00AM'

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Answer 6

thank you sir :)

works like magic

Answer 7

same as query of mr. jayakumaru

just a minor revision :)

the where Clause must be something like this.

(cast((left(datecommitted,12)+' '+right(timecommitted,7)) as datetime) >= '01/30/2014 15:00:00' AND cast((left(datecommitted,12)+' '+right(timecommitted,7)) as datetime) <= '02/11/2014 01:00:00')

instead of this.

(cast((left(datecommitted,12)+' '+right(timecommitted,7)) as datetime))

Answer 8

can't make it work... its says that their is no TIME datatype in sql server...

Answer 9

can't make it work... its says that their is no TIME datatype in sql server...

TIME data was added from 2008 version onwards.

http://msdn.microsoft.com/en-us/library/bb677243.aspx

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.