On 3 Mar 2007, at 15:21, Dennis Birch wrote: > On 3/3/07, Ian M. Jones <[EMAIL PROTECTED]> wrote: >> >> On 3 Mar 2007, at 03:49, Dennis Birch wrote: >> >>> I've saved my REALSQLDatabase-based application's date data in date >>> columns (e.g. field.DateValue = d). Now how do I write an SQLSelect >>> statement that can compare a given date with what I've saved in the >>> database? You obviously can't write "SELECT * FROM myTable WHERE >>> entryDate = d. I've tried using "d.SqlDate", but that doesn't work. >>> What does? >> >> You generally have to quote dates, so the following should work: >> >> strSQL = "SELECT * FROM myTable WHERE entryDate = '" + d.SQLDate + >> "'" >> >> Is that what you've tried already? > > Yes. I've tried it with single and double quotes. I've tried it with a > SQL date as a string literal. Nothing works. If I specify a date, I > get 0 records when I use a known good date. > > I can see that the dates are stored in the table in SQL date format by > selecting the table contents with no conditions (SELECT * FROM > de_DataEntryTable) and showing every field's stringvalue. So I'm > confused as to why I can't select on a date using the following: > SELECT * FROM DataEntryTable WHERE de_EntryDate = "2002-10-28".
Oops, sorry Dennis, I meant to write: strSQL = "SELECT * FROM myTable WHERE date(entryDate) = '" + d.SQLDate + "'" You have to use a date function when doing date comparisons. You'll find this is the same for most database engines, unless you supply a date string in the full native format you have to make sure both sides are in the same format. So the following might work as it includes the time portion which is inherent in the SQLite date type: strSQL = "SELECT * FROM myTable WHERE entryDate = '" + d.SQLDateTime + "'" But you're better off not making assumptions about the native format, it could change, so always use a date function to ensure you have a known format. The following page is a good source of date related functions used by SQLite/REALSQLDatabase: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions -- Ian M. Jones ___________________________________ http://www.imijsoft.com http://www.ianmjones.net (blog) _______________________________________________ Unsubscribe or switch delivery mode: <http://www.realsoftware.com/support/listmanager/> Search the archives: <http://support.realsoftware.com/listarchives/lists.html>
