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

Question

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

What database system is this, and why do your times have three zeros in the "seconds" field? — James McNellis
– James McNellis, Commented Oct 19, 2009 at 2:39
oh, that should have been the milliseconds column. my bad. SQL2005 — Dale
– Dale, Commented Oct 19, 2009 at 2:43
What do you get? Every question asking why results are not as expected should have (1) expected results and (2) actual results. — paxdiablo
– paxdiablo, Commented Oct 19, 2009 at 2:48
From my inital statement, I expect to get 10.1 and 10.3, what I get is 10.3 — Dale
– Dale, Commented Oct 19, 2009 at 2:51
What do you get from SELECT Value FROM [table] WHERE Datetime = '2009-10-29 10:00:00'? — Larry Lustig
– Larry Lustig, Commented Oct 19, 2009 at 2:56

OMG Ponies · Accepted Answer · 2009-10-19 03:27:52Z

1

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')

edited Oct 19, 2009 at 3:27

answered Oct 19, 2009 at 2:51

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dale Over a year ago

So why doesn't my statment work? Can you not have multiple times in a single WHERE clause?

Dale Over a year ago

ok, your union all statment works and i get the data I expected (10.1, 10.3). The second statment with the OR in the WHERE clause does not work, all I get is values returned by the OR statment

Tomas Markauskas · Accepted Answer · 2009-10-19 02:48:22Z

0

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')

edited Oct 19, 2009 at 2:48

answered Oct 19, 2009 at 2:41

Tomas Markauskas

11.6k2 gold badges35 silver badges35 bronze badges

2 Comments

Dale Over a year ago

that answere only returns data from the OR part of the WHERE statment and seems to ignore the first part of the WHERE statement.

Tomas Markauskas Over a year ago

What happens if you replace Datetime >= '2009-10-29 10:00:00' with Datetime > '2009-10-29 09:59:59'?

paxdiablo · Accepted Answer · 2009-10-19 03:08:02Z

0

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.

edited Oct 19, 2009 at 3:08

answered Oct 19, 2009 at 3:01

paxdiablo

889k243 gold badges1.6k silver badges2k bronze badges

2 Comments

Dale Over a year ago

Interesting. Yes, datetime columns. Your result is what I would have expected from sql

paxdiablo Over a year ago

Yeah, timestamp and varchar(19) give the expected results under DB2 but, then again, that just confirms my belief that no other DBMS even comes close :-)

Dave Barker · Accepted Answer · 2009-10-19 03:09:44Z

0

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?

answered Oct 19, 2009 at 3:09

Dave Barker

6,4972 gold badges27 silver badges26 bronze badges

6 Comments

Dale Over a year ago

yes, your demo worked for me also, but query doesnt. How do I post my full statment that I am actually working with?

paxdiablo Over a year ago

Put it in an answer if you can't edit your question. One of us will copy it up for you, then delete your answer.

Dave Barker Over a year ago

You may want to edit your question to include a full example that demonstrates the problem.

Dave Barker Over a year ago

The dataset posted doesn't match the SQL. I'm guessing the dataset is from your AnalogTag table. Are you sure the 2009-08-01 00:00:00.000 record exists in the history table with the right version and retrievalmode? Alternatively could you update your dataset with the exact output from the select statement without the not between?

Dale Over a year ago

ok. I think I have figured it out. The sql query is actually passed to another storage/retireval database. I think that is where the problem is. If I retrieve my data into a temporary table, and then apply the NOT BETWEEN part to that data, it works fine.

|

Dale · Accepted Answer · 2009-10-19 03:45:22Z

0

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!

answered Oct 19, 2009 at 3:45

Dale

111 silver badge4 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

2 Comments

2 Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

2 Comments

2 Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related