And that, my friend, was the missing link! Thank you!
Rick #>-----Original Message----- #>From: sqlite-users-boun...@sqlite.org #>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of David Bicking #>Sent: Monday, August 03, 2009 1:57 PM #>To: General Discussion of SQLite Database #>Subject: Re: [sqlite] Date Comparisons SQL #> #>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 #> #> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
