StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to select data using multiple times in the 'where' clause
text
Body
copied!<p>I have two columns of data in a SQL 2005 DB</p> <pre><code>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' </code></pre> <p>I want to </p> <pre><code>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' </code></pre> <p>I would have expected to get</p> <pre><code>10.1 10.3 </code></pre> <p>But this is not the case... help?</p> <p>Ok, Here is the actual sql statment I am working with;</p> <pre><code>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' </code></pre> <p>And here is the data set WITHOUT the NOT BETWEEN statment applied to it: <code><pre> 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 </pre></code></p> <p>And here is the data WITH the NOT BETWEEN appied to it</p> <p><code><pre> 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 </pre></code></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload