How to select data using multiple times in the 'where' clause

Question

Hi guys, I have two columns of data in a SQL 2005 DB

Datetime                Value
'2009-10-29 10:00:00' ' 10.1'
'2009-10-29 10:15:00' ' 10.2'
'2009-10-29 10:30:00' ' 10.3'
'2009-10-29 10:45:00' ' 10.4'

I want to

SELECT Value
FROM [table]
WHERE Datetime >= '2009-10-29 10:00:00'
AND (Datetime NOT BETWEEN '2009-10-29 10:14:00' AND '2009-10-29 10:16:00' )
AND Datetime < '2009-10-29 10:35:00'

I would have expected to get

10.1
10.3

But this is not the case... help?

Ok, Here is the actual sql statment I am working with;

Declare @Tagname varchar(25)
Set @Tagname = '52_RTU_#RE1002'

SELECT  DateTIme,(Runtime.dbo.v_AnalogHistory.Value), @Tagname As Tagname,0
FROM Runtime.dbo.AnalogTag INNER JOIN Runtime.dbo.v_AnalogHistory ON Runtime.dbo.AnalogTag.TagName = Runtime.dbo.v_AnalogHistory.TagName
WHERE(Runtime.dbo.v_AnalogHistory.TagName IN (@Tagname)) 
AND(Runtime.dbo.v_AnalogHistory.wwVersion = 'Latest') 
AND(Runtime.dbo.v_AnalogHistory.wwRetrievalMode = 'Full') 
AND Datetime >= '2009-08-01 00:00:00' 
AND (Datetime NOT BETWEEN '2009-08-01 10:27:00' AND '2009-08-01 11:30:00')
AND Datetime < '2009-08-01 11:35:00'

And here is the data set WITHOUT the NOT BETWEEN statment applied to it:

2009-08-01 00:00:00.000 0.72 52_RTU_#RE1002
2009-08-01 10:25:15.300 0.44 52_RTU_#RE1002
2009-08-01 10:27:22.350 0.5 52_RTU_#RE1002
2009-08-01 10:27:25.350 0.56 52_RTU_#RE1002
2009-08-01 10:27:27.360 0.62 52_RTU_#RE1002
2009-08-01 10:27:28.760 0.68 52_RTU_#RE1002
2009-08-01 10:27:30.360 0.74 52_RTU_#RE1002
2009-08-01 10:27:31.560 0.8 52_RTU_#RE1002
2009-08-01 10:27:32.760 0.87 52_RTU_#RE1002
2009-08-01 10:27:33.960 0.94 52_RTU_#RE1002
2009-08-01 10:27:35.370 1 52_RTU_#RE1002
2009-08-01 10:27:36.360 1.05999992370605 52_RTU_#RE1002
2009-08-01 10:27:37.570 1.13 52_RTU_#RE1002
2009-08-01 10:27:38.760 1.19000007629395 52_RTU_#RE1002
2009-08-01 10:27:40.360 1.25 52_RTU_#RE1002
2009-08-01 10:27:41.760 1.31 52_RTU_#RE1002
2009-08-01 10:27:43.560 1.37 52_RTU_#RE1002
2009-08-01 10:27:46.360 1.43 52_RTU_#RE1002
2009-08-01 10:27:57.580 1.37 52_RTU_#RE1002
2009-08-01 10:28:00.380 1.31 52_RTU_#RE1002
2009-08-01 10:28:02.580 1.25 52_RTU_#RE1002
2009-08-01 10:28:04.980 1.19000007629395 52_RTU_#RE1002
2009-08-01 10:28:07.390 1.13 52_RTU_#RE1002
2009-08-01 10:28:09.590 1.07000007629395 52_RTU_#RE1002
2009-08-01 10:28:11.810 1.01 52_RTU_#RE1002
2009-08-01 10:28:14.480 0.95 52_RTU_#RE1002
2009-08-01 10:28:16.630 0.89 52_RTU_#RE1002
2009-08-01 10:28:19.430 0.83 52_RTU_#RE1002
2009-08-01 10:28:21.830 0.77 52_RTU_#RE1002
2009-08-01 10:28:24.850 0.71 52_RTU_#RE1002
2009-08-01 10:28:28.240 0.65 52_RTU_#RE1002
2009-08-01 10:28:31.450 0.589999961853027 52_RTU_#RE1002
2009-08-01 10:28:35.250 0.529999961853027 52_RTU_#RE1002
2009-08-01 10:28:39.460 0.47 52_RTU_#RE1002
2009-08-01 10:28:44.470 0.41 52_RTU_#RE1002
2009-08-01 10:28:49.860 0.35 52_RTU_#RE1002
2009-08-01 10:28:56.870 0.29 52_RTU_#RE1002
2009-08-01 10:29:05.880 0.23 52_RTU_#RE1002
2009-08-01 10:29:17.890 0.17 52_RTU_#RE1002
2009-08-01 10:29:32.320 0.11 52_RTU_#RE1002
2009-08-01 10:29:57.930 0.05 52_RTU_#RE1002
2009-08-01 10:32:46.360 0.01 52_RTU_#RE1002
2009-08-01 10:37:46.360 0.01 52_RTU_#RE1002
2009-08-01 10:42:46.260 0.01 52_RTU_#RE1002
2009-08-01 10:47:46.310 0.01 52_RTU_#RE1002
2009-08-01 10:52:46.360 0.01 52_RTU_#RE1002
2009-08-01 10:57:46.230 0.01 52_RTU_#RE1002
2009-08-01 11:02:46.300 0.01 52_RTU_#RE1002
2009-08-01 11:07:47.230 0.01 52_RTU_#RE1002
2009-08-01 11:12:47.370 0.03 52_RTU_#RE1002
2009-08-01 11:17:47.260 0.04 52_RTU_#RE1002
2009-08-01 11:22:12.490 0.11 52_RTU_#RE1002
2009-08-01 11:22:47.300 0.14 52_RTU_#RE1002
2009-08-01 11:27:47.360 0.14 52_RTU_#RE1002
2009-08-01 11:28:40.610 0.17 52_RTU_#RE1002
2009-08-01 11:32:48.330 0.13 52_RTU_#RE1002
2009-08-01 11:34:34.680 0.23 52_RTU_#RE1002

And here is the data WITH the NOT BETWEEN appied to it

2009-08-01 11:32:48.330 0.13 52_RTU_#RE1002 0
2009-08-01 11:34:34.680 0.23 52_RTU_#RE1002 0

Answer 1

You can try something like this:

SELECT Value
FROM [table]
WHERE (Datetime >= '2009-10-29 10:00:00' AND Datetime < '2009-10-29 10:14:00')
OR (Datetime > '2009-10-29 10:16:00' AND Datetime < '2009-10-29 10:35:00')

Answer 2

Works:

SELECT t.value
  FROM TABLE t
 WHERE t.datetime BETWEEN '2009-10-29 10:00:00' AND '2009-10-29 10:13:59'
UNION ALL
SELECT t.value
  FROM TABLE t
 WHERE t.datetime BETWEEN '2009-10-29 10:16:01' AND '2009-10-29 10:34:59'

...now try:

SELECT t.value
  FROM TABLE t
 WHERE (t.datetime BETWEEN '2009-10-29 10:00:00' AND '2009-10-29 10:13:59'
        OR t.datetime BETWEEN '2009-10-29 10:16:01' AND '2009-10-29 10:34:59')

Answer 3

Are those actually datetime columns of some form of varchar? I ask because a similar query in DB2 works fine:

drop table xx; commit;                                
create table xx (val1 integer, val2 integer); commit;                                
insert into xx values (1000,101);
insert into xx values (1015,102);
insert into xx values (1030,103);
insert into xx values (1045,104);
commit;
select * from xx
where val1 >= 1000
and (val1 not between 1014 and 1029)   
and val1 < 1035;

This returns:

VAL1  VAL2              
1000   101              
1030   103              
DSNE610I NUMBER OF ROWS DISPLAYED IS 2

In fact, even with timestamps, I get the right result:

drop table drlan.xx;                                       
commit;                                                    
create table drlan.xx (                                    
  val1 timestamp,                                          
  val2 varchar(4)                                          
);                                                         
commit;                                                    
insert into drlan.xx values ('2009-10-29 10:00:00','10.1');
insert into drlan.xx values ('2009-10-29 10:15:00','10.2');
insert into drlan.xx values ('2009-10-29 10:30:00','10.3');
insert into drlan.xx values ('2009-10-29 10:45:00','10.4');
commit;                                                    
select * from drlan.xx                                     
where val1 >= '2009-10-29 10:00:00'                        
and (val1 not between '2009-10-29 10:14:00'                
                  and '2009-10-29 10:16:00')               
and val1 < '2009-10-29 10:35:00';                          

VAL1                        VAL2              
2009-10-29-10.00.00.000000  10.1      
2009-10-29-10.30.00.000000  10.3      
DSNE610I NUMBER OF ROWS DISPLAYED IS 2

This leads me to believe that there's some sort of problem with SQL Server.

Answer 4

The following works for me in SQL Server 2005.

create table xx (val1 datetime, val2 varchar(10)); 

insert into xx values ('2009-10-29 10:00:00', ' 10.1');
insert into xx values ('2009-10-29 10:15:00', ' 10.2');
insert into xx values ('2009-10-29 10:30:00', ' 10.3');
insert into xx values ('2009-10-29 10:45:00', ' 10.4');


SELECT val2
FROM [xx]
WHERE val1 >= '2009-10-29 10:00:00'
AND (val1 NOT BETWEEN '2009-10-29 10:14:00' AND '2009-10-29 10:16:00' )
AND val1 < '2009-10-29 10:35:00'

but if you perform

insert into xx values ('2009-10-29 9:59:59.999', ' 10.8');

then this displays in SQL Server Management Studio as

2009-10-29 10:00:00.000  10.8

Are you sure of the unrounded values?

Answer 5

Ok, as it turns out the storage/retrieval engine (a product called INsql) that sql is getting the data from appears to not work with the multiple time in statment thing. If i select the whole data set into a temporary table and work on it there with sql, the NOT BETWEEN statment works fine. Thank you all for your help in narrowing this down!