get data between,before and after time

Question

i am developing a query  ..am giving some examples;

id  start        end 

1   9             9.30

2    10        14.00

3       9            18.30 --- facing difficulty getting this id 

4       15           15.30 

the id i will be 2,3

select id from tbl.forms where startTime >= '2012-04-27 10:00:00.000' or startTime <= '2012-04-27 12:30:00.000'))

endTime >= '2012-04-27 12:30:00.000 ' or  endTime <=' 2012-04-27 10:00:00.000')) --i only get 2 as id 

i have tried using between also i cant get the Exact  id

  

Answer 1

What version are  you using? Since SQL Server 2008 we have DATE and TIME datatype... Anyway

CREATE TABLE #t(id INT,start INT,[end] VARCHAR(5))

INSERT INTO #t VALUES (1,9,9.30)
INSERT INTO #t VALUES (2,10,14.00)
INSERT INTO #t VALUES (1,9,18.30)
INSERT INTO #t VALUES (2,15,15.30)

SELECT * FROM
(
SELECT *,CAST('2012-04-27 '+CAST(start AS VARCHAR(2))+':'+[end] 
AS DATETIME) dt FROM #t 
) AS Der WHERE dt
 <= '2012-04-27 12:30:00.000'

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

is it store as 18.3 or 18:30 ?

---

Regards,
Ahmed Ibrahim
SQL Server Setup Team
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread.

Answer 3

am using sql server 2008 r2 datetime ...its 18.30 pm  ! 

Answer 4

can you provide the table structure with sample data ?

---

Mark as Answer If Reply Was Helpful
Thanks
Kuldeep Bisht
Technical Lead @ Simplion Technologies
Blog : www.dbsimplified.com