Thanks. Changing the date format when exporting from Access seems to have done the trick.
Timothy A. Sawyer, CISSP Managing Director MBD Solutions Phone: (603) 546-7132 Web: http://www.mybowlingdiary.com Email: [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Monday, November 03, 2008 8:06 AM To: [email protected] Subject: Re: [sqlite] Problem with dates "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 _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
