Re: [sqlite] Problem with between clause and dates
Mike Martin wrote: >>> select tsid from recordings where '2010-03-16 23:05:00' between start and >>> end' > > It should return nothing because the time 2010-03-16 23:06:00 is later > than the end time of 2010-03-16 23:05:00 I don't see 23:06 anywhere in your statement nor in your data. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with between clause and dates
On Wed, Mar 17, 2010 at 12:18:44PM +, Mike Martin scratched on the wall: > > >> select tsid from recordings where '2010-03-16 23:**05**:00' between > It should return nothing because the time 2010-03-16 23:**06**:00 is later > than the end time of 2010-03-16 23:**05**:00 Let me guess... yet another typo. In your post or in your code? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with between clause and dates
Mike Martin wrote: > I am getting occasional incorrect results using between operator > > example > > table structure > recordings > ID > tsid > start > end > > sample time= 2010-03-16 23:06:00 > sample start= 2010-03-16-22:00:00 > sample end= 2010-03-16 23:05:00 Do you actually have a dash between date and time portion in your data (in your example, one appears in start but not in time or end)? If you have it inconsistently, that would throw off any comparisons. > select tsid from recordings where '2010-03-16 23:05:00' between start and end' You have an extra, mismatched single quote at the end. > This query should return nothing, so can anyone explain why it is > recording the tsid Why should this query return nothing? BETWEEN operator is inclusive. "x between a and b" is equivalent to "x>=a and x<=b". -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with between clause and dates
On 17. 3. 2010 11:49, Mike Martin wrote: > sample time= 2010-03-16 23:06:00 > sample start= 2010-03-16-22:00:00 > sample end= 2010-03-16 23:05:00 > select tsid from recordings where '2010-03-16 23:05:00' between start and end' > > This query should return nothing, so can anyone explain why it is > recording the tsid > > thanks > > as far as i know XX BETWEEN YY AND ZZ is equal to XX>=YY and XX<=ZZ so its ok that it returns something. i always use it like that (but on Sybase database) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with between clause and dates
On 17 Mar 2010, at 10:49am, Mike Martin wrote: > sample time= 2010-03-16 23:06:00 > sample start= 2010-03-16-22:00:00 > sample end= 2010-03-16 23:05:00 > select tsid from recordings where '2010-03-16 23:05:00' between start and end' > > This query should return nothing, so can anyone explain why it is > recording the tsid Your sample start time has a hyphen between date and time. Is this mistyping when you wrote your email or is it in your data file like that ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
