sqlite between question

Question

Before I rewrite an app I figured it's best to ask this question first.

With sqlite, is it possible to do a between statement like this?

select * from database where date between '2010-10-01' and '2010-10-31'

As a side question, how do I get the last date of a month in javascript using the above format?

Answer 1

Yes you can (from the docs):

The BETWEEN operator

The BETWEEN operator is logically equivalent to a pair of comparisons. x BETWEEN y AND z is equivalent to x>=y AND x<=z except that with BETWEEN, the x expression is only evaluated once. The precedence of the BETWEEN operator is the same as the precedence as operators == and != and LIKE and groups left to right.

As for your last question, you may want to do the following:

... WHERE your_date >= '2010-10-01' AND your_date < date('2010-10-01', '+1 month')

Further reading:

• sqlite Date and Time Functions

Answer 2

is it possible to do a between statement like this?

select * from database where date between '2010-10-01' and '2010-10-31'

Yes, it is.

Answer 3

Yes you can use it, also you can use the following if you have other date format

SELECT *
FROM orders
WHERE order_date between strftime('%Y/%m/%d', '2003/01/01')
AND strftime('%Y/%m/%d', '2003/12/31');

Answer 4

Answer to the side question would be something like this:

 Date.prototype.toSQLDateString = function() {
     function pad(n) { return n < 10 ? '0' + n : n }
     return this.getFullYear() + '-' + pad(this.getMonth() + 1) + '-' + pad(this.getDate());
 };

 Date.prototype.setDateToLastOfTheMonth = function() {        
     for (var i = 31; i > 25; i++) {
       this.setDate(i);
       if (this.getDate(i) != 1) {
         return;
       }
     }
 };