RATE THIS ANSWER
0
Click to Vote:
- 0
0
Something like this should do the trick:
SELECT * FROM tblTest
WHERE DATEADD(ss,CAST(LEFT(time,10) AS INT),'01/01/1970')
BETWEEN '<some time>' AND '<some time>';
----------------- kccrosser
While CarlosDL's query will work, it will result in a full table scan on every call. A more efficient approach is to compute the time field's min/max values and then run the query. Example (assume you want all records between 1/1/2009 and 1/31/2009, inclusive):
declare @timebase int
declare @mintime int
declare @maxtime int
set @timebase = CONVERT(DATETIME,'1970-01-01 00:00:00')
set @mintime = DATEDIFF(SECOND, @timebase, CONVERT(DATETIME,'2009-01-01 00:00:00))
set @maxtime = DATEDIFF(SECOND, @timebase, CONVERT(DATETIME,'2009-02-01 00:00:00))
select * from tblTest
where time between @mintime and @maxtime
This approach can use an index on Time to efficiently query the data.
Whenever possible, avoid using functions (including data type conversions) in the Where clause, as these will nearly always cause the query optimizers to be unable to use indexes, and thus will result in table scans.
Last Answered:
Nov 2 2009 8:48 PM GMT by Kccrosser 
1835 pts.
Latest Contributors: Carlosdl 29710 pts.
