"Timothy A. Sawyer" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > My SQLite database was imported from an Access database where the > dates (in > Access) were stored as mm/dd/yyyy. My application is written in Java > where I > am using the DateFormat method to convert the date to mm/dd/yy. > However when > I do a select based on that date, for example "SELECT * FROM > tblEventInfo > WHERE fldDateBowled >= '01/01/08' it returns all the rows in the > database.
'01/01/08' is just a string literal. You are doing string comparisons, in alphabetical order. > Reading the documentation, I found that dates in SQLite are in > 'yyyy/mm/dd/' > format. SQLite doesn't have a dedicated date type at all. Instead, it has a set of functions for manipulating dates, and a number of formats these functions recognize: http://sqlite.org/lang_datefunc.html You may choose any of these formats to represent your dates internally - but you have to be consistent about it. > Still, doing "SELECT * FROM tblEventInfo WHERE fldDateBowled > >= '2008-01-01'" does not yield the results I expect. '2008-01-01' is still just a string literal, and you are still doing simple string comparisons. Now, for two dates in YYYY-MM-DD format, an alphabetical comparison happens to order them correctly as dates, too. So, if you represent all your dates in this format - actual dates stored in your tables as well as literals used in the query - then the statement above would do the right thing. > What do I need to do with the dates when I import the data from > Access? Do I > need to convert them to YYYY-MM-DD? It would definitely help. That's what I'd recommend, unless you have a good reason not to. Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
