On 22 May 2012, at 7:25pm, Marcelo Paiva <[email protected]> wrote:
> data -> date type -> example -> 22/05/2012 -> diferent -> "22/05/2012" > > question/sentence: select *from tcontsif01 where data>='01/01/2012' and > data<='01/05/2012' SQLite has no datatype for dates. Your data '01/01/2012' and '22/05/2012' is being stored as text. So a test '31/12/2012' > '01/01/2013' is true because of the first characters: '31' > '01' like 'hut' > 'house' in a dictionary. I recommend that when you store dates in your database you store them in a sortable order. For instance you can store the date '22/05/2012' as '2012/05/22'. If you do this, then a SELECT command SELECT * FROM tcontsis01 WHERE data >= '2012/01/01' AND data <= '2012/05/01' will work perfectly. An alternative would be to store dates as day numbers, either using unix epoch (real numbers) or Julian date numbers (integers). In all these cases you search will, of course, be faster if you have the 'data' column in an index. If changing the format of the data in your table is very difficult, then you can continue to store them in their current order and use SQLite's date functions in your SELECT commands: <http://www.sqlite.org/lang_datefunc.html> However, this will make searches far slower because SQLite will have to convert each data entry as it does each search every time it does a search. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
