select *
from entry
where bankdate between date('now', 'localtime', 'start of month')
and date('now', 'localtime', 'start of month', '+1 month',
'-1 second');
assuming that your bankdate contains a datestring formatted as yyyy-mm-dd in
localtime. Without the 'localdate' you get UTC, which may cause consternation
and confusion depending on your timezone and when you execute the query.
sqlite> select date('now', 'localtime', 'start of month'),
...> date('now', 'localtime', 'start of month', '+1 month', '-1
second');
2013-09-01|2013-09-30
sqlite> select datetime('now', 'localtime', 'start of month'),
...> datetime('now', 'localtime', 'start of month', '+1 month', '-1
second');
2013-09-01 00:00:00|2013-09-30 23:59:59
You may want to ensure that the date functions are executed only once rather
than per-row by re-phrasing the query something like this: (I am not sure if
the optimizer now knows that date result is constant and only needs to be
executed once for the whole query or not -- it did not used to unless the date
was retrieved in a scalar query such as either of the below):
select *
from entry,
(select date('now', 'localtime', 'start of month') as begindate,
date('now', 'localtime', 'start of month', '+1 month', '-1
second') as enddate) as daterange
where bankdate between begindate and enddate;
which creates a temporary table with the start and end dates in it, and uses
that table in join constraints. Obviously this table will end up as the outer
loop.
select *
from entry
where bankdate between (select date('now', 'localtime', 'start of month'))
and (select date('now', 'localtime', 'start of month', '+1
month', '-1 second'));
which will force the VDBE compiler to emit once conditionals around the date
functions since it is clear that they represent a scalar constant.
The join format is somewhat more useful if you will be referring to the
resulting dates more than once or in a join rather than a simple single table
select ...
> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of jwzumwalt
> Sent: Wednesday, 11 September, 2013 23:34
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Select with dates
>
> I have not used the date function in select statements before.
> I have valid entries for the current month, what am I doing wrong?
>
> SELECT * FROM "entry" WHERE
> bankdate > date('now','end of month','-1 month')
> AND bankdate < date('now','start of month','+1 month')
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Select-with-dates-tp71215.html
> Sent from the SQLite mailing list archive at Nabble.com.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
