Average value after 3 time intervals.
-
I have a simple table with 3 fields: ID, Tag1, Data ID Tag1 Data ________________________________ 1 2 6/1/2009 22:00 2 1 6/1/2009 23:00 3 2 6/2/2009 6:00 4 3 6/2/2009 7: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/
-
I have a simple table with 3 fields: ID, Tag1, Data ID Tag1 Data ________________________________ 1 2 6/1/2009 22:00 2 1 6/1/2009 23:00 3 2 6/2/2009 6:00 4 3 6/2/2009 7: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/
Hi, I'm no SQL expert however IMO
DATEPART(hour, DataTime) BETWEEN DATEPART(hour,'10:30:00 PM') AND DATEPART(hour,'06:30:00 AM')can't be right since that ignores the half hours completely. Can't you just use some function to get the time, something likeTIME(DataTime) NOT BETWEEN '06:30:00' AND '22:30:00'? BTW: I felt a need to eliminate the 12hour timing, and to invert the statement. :)Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
-
Hi, I'm no SQL expert however IMO
DATEPART(hour, DataTime) BETWEEN DATEPART(hour,'10:30:00 PM') AND DATEPART(hour,'06:30:00 AM')can't be right since that ignores the half hours completely. Can't you just use some function to get the time, something likeTIME(DataTime) NOT BETWEEN '06:30:00' AND '22:30:00'? BTW: I felt a need to eliminate the 12hour timing, and to invert the statement. :)Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
Thanks for your answer. Unfortunately, I can't waive these time intervals. What I don't understand is why SELECT ID FROM dbo.TableTest WHERE Data BETWEEN CONVERT(DATETIME,(CONVERT(varchar(10),Data, 101)+ ' 10:00:00 PM'),101) AND CONVERT(DATETIME,(CONVERT(varchar(10),DATEADD(DAY,1,Data), 101)+ ' 06:00:00 AM'),101) return this: and not return: ID ID __ __ 1 1 2 2 19 3 20 19 21 20 34 21 35 22 23 24 25 26 &nbs