SELECT not working!!!

[scadagui]

I have a simple table (TableTest) with 3 fields: ID, Tag1, Data

ID Tag1 Date
________________________________
1 2 6/1/2009 22:00
2 1 6/1/2009 23:00
3 2 6/2/2009 1:00
4 3 6/2/2009 6:00
5 2 6/2/2009 8:00
6 2 6/2/2009 9:00
7 2 6/2/2009 10:00
8 1 6/2/2009 11:00
9 2 6/2/2009 11:30
10 1 6/2/2009 13:00
11 2 6/2/2009 14:00
12 2 6/2/2009 15:00
13 2 6/2/2009 16:00
14 1 6/2/2009 17:00
15 2 6/2/2009 18:00
16 1 6/2/2009 19:00
17 1 6/2/2009 20:00
18 2 6/2/2009 21:00
19 2 6/2/2009 22:00
20 3 6/2/2009 23:00
21 1 6/2/2009 23:59
22 2 6/3/2009 1:00
23 3 6/3/2009 2:00
24 2 6/3/2009 3:00
25 3 6/3/2009 4:00
26 2 6/3/2009 5:00
27 3 6/3/2009 6:00
28 2 6/3/2009 7:00
29 3 6/3/2009 8:00
30 2 6/3/2009 9:00
31 3 6/3/2009 10:00
32 2 6/3/2009 11:00
33 1 6/3/2009 11:55
34 2 6/3/2009 22:00
35 2 6/3/2009 23:00

Must I do a select for '10.00 PM - 6.00 AM' interval in a time period selected by user.
The following code...

SELECT ID,Tag1
FROM TableTest
WHERE Date BETWEEN CONVERT(DATETIME,(CONVERT(varchar(10),Date, 101)+' 10:00:00 PM'),101) AND CONVERT(DATETIME,(CONVERT(varchar(10),Date+1, 101)+' 6:00:00 AM'),101)

the result is (is not correct):
ID Tag1
-------------
1 2.0
2 1.0
19 2.0
20 3.0
21 1.0
34 2.0
35 2.0

Why not get the result (is correct):

ID Tag1
-------------
1 2.0
2 1.0
3 2.0
4 3.0
19 2.0
20 3.0
21 1.0
22 2.0
23 3.0
24 2.0
25 3.0
26 2.0
27 3.0
34 2.0
35 2.0

I wrong somewhere?
Thanks

 

[gmmastros]

Your approach here is wrong.

What I would do is return all rows where the time part is earlier (or equal to) 6 AM OR the time part is later than (or equal to) 10 PM.

The trickiest part here is to remove the date part from the date/time column but to still have the DateTime data type.

To remove the date:

[tt][blue]
Select Date - DateAdd(Day, DateDiff(Day, 0, Date), 0)
From TestTable
[/blue][/tt]

Now that we have that part figured out, the rest is pretty easy:

Select * 
From   TestTable
Where  (Date - DateDiff(Day, 0, Date) <= '6:00 AM')
       or (Date - DateDiff(Day, 0, Date) >= '10:00 PM')

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Jamfool]

SELECT * FROM TableTest
WHERE DATEPART(HH,[Date]) BETWEEN 6 AND 22
AND <Date range>...

 

[scadagui]

Thanks a lot George.

 

[scadagui]

Excuse me as I returned.I'm not expert in SQL.
I tried to make an average for 'Tag1' field between '10.00 PM - 6.00 AM' where 'Date' between '6/1/2009 22:00' and '6/3/2009 23:00' (of TableTest above).
Can you help me and in this issue?
Thanks again.