On Tue, Sep 2, 2014 at 3:48 PM, jose isaias cabrera <[email protected]
> 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
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
