On Tue, Sep 2, 2014 at 3:48 PM, jose isaias cabrera <jic...@cinops.xerox.com > wrote:
> > Greetings! > > I know that SQLite dates are of the form YYYY-MM-DD and I like that. :-) > I want to find out why these are working. > SQLite does not have a special "date" type. SQLite stores dates as either strings, or integers, or floating point numbers. In your case it is storing and comparing them as strings. > > create table t (a date, val integer); > If you instead said: create table t(a TEXT, val integer); Would you then understand how it works? If so, then my explanation is that it works *exactly* the same why when you substitute "date" for "text" in the table declaration. > insert into t values ('2010-01-01', 10); > insert into t values ('2010-1-1', 10); > insert into t values ('2010-1-01', 10); > insert into t values ('2010-02-01', 10); > insert into t values ('2010-2-01', 10); > > select sum(val) from t where a BETWEEN '2010-01-01' AND '2010-01-31'; > select sum(val) from t where a BETWEEN '2010-01-01' AND '2010-02-01'; > select sum(val) from t where a BETWEEN '2010-01-01' AND '2010-12-31'; > > sqlite> select sum(val) from t where a BETWEEN '2010-01-01' AND > '2010-01-31'; > 10 > sqlite> select sum(val) from t where a BETWEEN '2010-01-01' AND > '2010-02-01'; > 20 > sqlite> select sum(val) from t where a BETWEEN '2010-01-01' AND > '2010-12-31'; > 40 > > Thoughts? Thanks. > > josé > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users