I think your problem is that you need to put the value in sDateTemp in quotes.
"WHERE Date < '" & sDateTemp & "'" or "WHERE Format$(Date, "yyyy-mm-dd") < '"& sDateTemp & "'" Without the quote, I think sqlite is subtracting the day from the month from the year, and comparing that number with the Date string. David --- On Mon, 8/3/09, Rick Ratchford <r...@amazingaccuracy.com> wrote: > From: Rick Ratchford <r...@amazingaccuracy.com> > Subject: Re: [sqlite] Date Comparisons SQL > To: "'General Discussion of SQLite Database'" <sqlite-users@sqlite.org> > Date: Monday, August 3, 2009, 2:51 PM > Okay, I think I understand what you > are saying. > > The native Date in a table without any additional > expressions is 'yyyy-mm-dd > 00:00:00'. > > Those "00:00:00" must be my problem. > > Rick > > > > > #>-----Original Message----- > #>From: sqlite-users-boun...@sqlite.org > > #>[mailto:sqlite-users-boun...@sqlite.org] > On Behalf Of Rick Ratchford > #>Sent: Monday, August 03, 2009 1:45 PM > #>To: 'General Discussion of SQLite Database' > #>Subject: Re: [sqlite] Date Comparisons SQL > #> > #>That's the clincer. > #> > #>The resulting DATE column is actually the format of > the > #>equation as well. > #> > #>I've attached a view of the results from the working > SQL > #>statement that does not perform the WHERE. > #> > #>"WHERE Format$(Date, 'yyyy-mm-dd') < sDateTemp" > does not work. > #> > #>Also, as stated in my previous post, I have sDateTemp > > #>formatted in the same format as that which is in the > table. > #> > #>That's why I'm puzzled. > #> > #>Rick > #> > #> > #> > #>#>-----Original Message----- > #>#>From: sqlite-users-boun...@sqlite.org > > #>#>[mailto:sqlite-users-boun...@sqlite.org] > On Behalf Of Igor Tandetnik > #>#>Sent: Monday, August 03, 2009 1:38 PM > #>#>To: sqlite-users@sqlite.org > #>#>Subject: Re: [sqlite] Date Comparisons SQL #> > #>Rick > #>Ratchford wrote: > #>#>> The Date is being stored as yyyy-mm-dd. Note > the > #>"Format$(Date, #>> 'yyyy-mm-dd') as Date" that > assures this. > #>#> > #>#>The "Date" that appears in the WHERE clause is > the value of > #>#>the Date column in the table, not the value of > the > #>expression #>with the "Date" > #>#>alias. You can't actually use aliases in the > WHERE clause. > #>#>You are confusing yourself by using the same > identifier > #>both #>for the column name and for the alias. > #>#> > #>#>You could write > #>#> > #>#>WHERE Format$(Date, 'yyyy-mm-dd') < sDateTemp > #> #>Or else, > #>express sDateTemp in the same format that you have > #>dates > #>stored in the table - the format you get when you just > #>run > #>"SELECT Date from mytable". > #>#> > #>#>Igor Tandetnik > #>#> > #>#> > #>#> > #>#>_______________________________________________ > #>#>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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
