Problem with Date Query - sql server 2008

Question

hi

i have this date's & Time's:

date1: 10/04/2010 - Time: 08:09

date2: 11/04/2010 - Time: 08:14

i need to show all the date's between 10/04/2010 time 06:00 and 11/04/2010 time 6:00

i write this: select * from MyTbl where ((Tdate BETWEEN '20100410' AND '20100411') and (Ttime BETWEEN '06:00' and '06:00'))

but i get empty table

thank's in advance

Answer 1

and time between '06:00' and '06:00'???
It will not return you any data obviously:)

Answer 2

That's not correct.

What your resultset returns is all the records that are valid between 10/04/2010 06:00 - 10/04/2010 06:00 or 11/04/2010 06:00 - 11/04/2010 06:00.

(Which means there are exactly 2 valid minutes for records to be retrieved).

You need to filter according to the full date including time.

Answer 3

I think you are looking for this:

select * 
from MyTbl 
where (Tdate = '20100410' AND Time >= '06:00') 
   OR (TDATE > '20100410' AND TDATE < '20100410')
   OR (Tdate = '20100411' AND Time <= '06:00')

Answer 4

If both columns are datetime, simply add them so you can do a "proper" range query

where Tdate +Ttime BETWEEN '2010-04-10 06:00:00' AND '2010-04-11 06:00:00'