You are comparing the values as strings.
Instead, format your dates as YYYY-MM-DD and use the date function to
convert strings to dates for comparison:
select date from envelope where date > date('2009-01-20') limit 3;
here are some examples:
sqlite> select date('2009-07-01') where
date('2009-07-01')>date('2009-06-01');
2009-07-01
sqlite> select date('2009-07-01') where
date('2009-07-01')<date('2009-06-01');
(no row returned)
-----Original Message-----
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paolo Pisati
Sent: Wednesday, January 21, 2009 6:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Date datatype
It seems i'm having an hard time with dates in sqlite:
sqlite> .schema
CREATE TABLE `envelope` (`smtp_id` int(10) NOT NULL, `date` date NOT
NULL, `time` time NOT NULL, `mailq_sndr` int(10) NOT NULL,
`delivery_sndr` int(10) NOT NULL, `customer_sndr` int(10) NOT NULL,
`rpath` varchar(250) NOT NULL, `domain_rcvr` varchar(200) NOT NULL,
`user_rcvr` varchar(250) NOT NULL, `size` int(10) NOT NULL, `res`
int(10) NOT NULL, `msg` varchar(250) NOT NULL, `ip` int(10) NOT NULL,
`vsmtp` varchar(250) NOT NULL, `retries` int(10) NOT NULL);
sqlite> select date from envelope where date > '2009/01/20' limit 3;
2009/1/7
2009/1/7
2009/1/7
why?
--
bye,
P.
_______________________________________________
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
