Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Extract data from sql table using timestamps range

Author  Topic 

Jone Doe
Starting Member

1 Post

Posted - 2014-04-21 : 14:25:09
Hello.

I probably have not very smart question because of its simplicity. However, I am a newbie in SQL. There is a simple SQL table ("mytable"). Say, it has 100 rows and 5 columns. One of the columns (say "time") contains timestamps across the whole day and the data in this column has the following format: hh:mm:ssAM/PM. So, the table looks like this:

time var1 var2 var3 var4

12:00:01PM value ...

12:00:05PM value

12:00:08PM value

12:00:20PM value

12:10:12PM value

...100 rows

How to create simple SQL request for extracting data between any 2 timestamps? For example, I need sub-table of the initial table containing all data values between 12:00:05PM and 12:00:20PM:

time var1 var2 var3 var4

12:00:05PM value

12:00:08PM value

12:00:20PM value



I consider something like this:

SELECT * FROM mytable WHERE time BETWEEN ...?

Please help me in writing this query. Thanks in advance for the reply.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-21 : 14:46:44
You could use BETWEEN '12:00:05' and '12:00:20'

or ...WHERE time >= '12:00:05' AND time < '12:00:21'

Sometimes the latter form is preferred since BETWEEN would exclude 12:00:20.001 (one millisecond after 12:20) or later
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-22 : 00:12:06


Notice that the BETWEEN operator can produce different result in different databases!
In some databases, BETWEEN selects fields that are between and excluding the test values.
In other databases, BETWEEN selects fields that are between and including the test values.
And in other databases, BETWEEN selects fields between the test values, including the first test value and excluding the last test value.

Therefore: Check how your database treats the BETWEEN operator!

For me it's worked fine



DECLARE @Temp TABLE(time DATETIME,var1 INT,var2 VARCHAR(20))
INSERT INTO @Temp Values('12:00:01PM',1,'value')
,('12:00:05PM',2,'value1')
,('12:00:08PM',3,'value2')
,('12:00:20PM',4,'value3')
,('12:00:30PM',5,'value4')
SELECT * FROM @Temp WHERE time BETWEEN '1900-01-01 12:00:01.000' AND '1900-01-01 12:00:08.000'




---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page
   

- Advertisement -