Re: [sqlite] Date Comparisons SQL

2009-08-03 Thread Olaf Schmidt 

"Rich Shepard"  schrieb im
Newsbeitrag news:alpine.lnx.2.00.0908031516300.3...@salmo.appl-ecosys.com...

> > It was set as String actually.
>
> Rick,
>
>That's the storage class; well, TEXT is the storage class.
Yep.

> > I believe this is a WRAPPER thing though.
Exactly.
The COM-wrapper handles Date-Column-Definitions
which are "declared" in the Create Table Statement
as "..., MyDate Date, ..." or "..., MyShortDate ShortDate, ..."
with something like an "auto-mapping" to the appropriate
vbDate Variable-Type (which at binary-level, in the language
is a Floatingpoint-Type with Double-precision - counting the
days since 1899-12-30, with the "percentage of a day"
encoded in the fractional part of this floatingpoint-number).

So, it's the wrapper who maps vbDate-Types to the SQLite-engines
Text-storage-class (using the standard-SQL "textdate-format",
'-mm-dd' for ShortDates and '-mm-dd hh:mm:ss'
for "full dates". The SQLite-engine itself does not know about
these "VB-Double-DateTypes "the wrapper hands out to the
application.

Just to shade some light on the topic... ;-)

Olaf



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Comparisons SQL

2009-08-03 Thread Rick Ratchford 
Olaf tells me that it's stored as Text-String.

Either as '-mm-dd' or '-mm-dd hh:mm:ss', depending on how I decide
to store my VB type dates.

Anyway, the original problem was solved. I simply neglected to address the
need for 'quotes' around my date variable.

Thanks Rich.

:-)
Rick


 
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rich Shepard
#>Sent: Monday, August 03, 2009 5:18 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Date Comparisons SQL
#>
#>On Mon, 3 Aug 2009, Rick Ratchford wrote:
#>
#>> It was set as String actually.
#>
#>Rick,
#>
#>   That's the storage class; well, TEXT is the storage class.
#>
#>> I believe this is a WRAPPER thing though. I'm programming 
#>in VB6 and 
#>> using Olaf's VB wrapper.
#>
#>   Oh. I know nothing about Microsoft languages (or operating 
#>systems for that matter), except that they're different. I do 
#>all my coding in C or Python.
#>
#>Rich
#>
#>-- 
#>Richard B. Shepard, Ph.D.   |  Integrity  
#>  Credibility
#>Applied Ecosystem Services, Inc.|Innovation
#><http://www.appl-ecosys.com> Voice: 503-667-4517  
#>Fax: 503-667-8863
#>___
#>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

Re: [sqlite] Date Comparisons SQL

2009-08-03 Thread Rich Shepard 
On Mon, 3 Aug 2009, Rick Ratchford wrote:

> It was set as String actually.

Rick,

   That's the storage class; well, TEXT is the storage class.

> I believe this is a WRAPPER thing though. I'm programming in VB6 and using
> Olaf's VB wrapper.

   Oh. I know nothing about Microsoft languages (or operating systems for
that matter), except that they're different. I do all my coding in C or
Python.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Comparisons SQL

2009-08-03 Thread Rick Ratchford 
It was set as String actually.

I've since changed it to ShortDate today.

I believe this is a WRAPPER thing though. I'm programming in VB6 and using
Olaf's VB wrapper.

:)
Rick
 
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rich Shepard
#>Sent: Monday, August 03, 2009 3:45 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Date Comparisons SQL
#>
#>On Mon, 3 Aug 2009, Rick Ratchford wrote:
#>
#>> The native Date in a table without any additional expressions is 
#>> '-mm-dd 00:00:00'.
#>
#>Rick,
#>
#>   That's a timestamp format. Did you specify the column as 
#>date or timestamp?
#>
#>Rich
#>
#>-- 
#>Richard B. Shepard, Ph.D.   |  Integrity  
#>  Credibility
#>Applied Ecosystem Services, Inc.|Innovation
#><http://www.appl-ecosys.com> Voice: 503-667-4517  
#>Fax: 503-667-8863
#>___
#>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

Re: [sqlite] Date Comparisons SQL

2009-08-03 Thread Rich Shepard 
On Mon, 3 Aug 2009, Rick Ratchford wrote:

> The native Date in a table without any additional expressions is
> '-mm-dd 00:00:00'.

Rick,

   That's a timestamp format. Did you specify the column as date or
timestamp?

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Comparisons SQL

2009-08-03 Thread Rick Ratchford 
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, "-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 
#>> '-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, '-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 -mm-dd. Note the 
#>> #>"Format$(Date, #>> '-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, '-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

Re: [sqlite] Date Comparisons SQL

2009-08-03 Thread David Bicking 
I think your problem is that you need to put the value in sDateTemp in quotes.

"WHERE Date < '" & sDateTemp & "'"

or "WHERE Format$(Date, "-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 '-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, '-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 -mm-dd. Note
> the 
> #>"Format$(Date, #>> '-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, '-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

Re: [sqlite] Date Comparisons SQL

2009-08-03 Thread Rick Ratchford 
Okay, I think I understand what you are saying.

The native Date in a table without any additional expressions is '-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, '-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 -mm-dd. Note the 
#>"Format$(Date, #>> '-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, '-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

Re: [sqlite] Date Comparisons SQL

2009-08-03 Thread Pavel Ivanov 
> I've attached a view of the results from the working SQL statement that does
> not perform the WHERE.

Attachments do not come through to this list.

> Also, as stated in my previous post, I have sDateTemp formatted in the same
> format as that which is in the table.

Could you elaborate: your sDateTemp is "the same format as in the
table" or "formatted as '-mm-dd'"? If the former then what the
format is? And format exactly in the table?

Pavel

On Mon, Aug 3, 2009 at 2:44 PM, Rick Ratchford<r...@amazingaccuracy.com> wrote:
> 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, '-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 -mm-dd. Note the "Format$(Date,
> #>> '-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, '-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

Re: [sqlite] Date Comparisons SQL

2009-08-03 Thread Rick Ratchford 
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, '-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 -mm-dd. Note the "Format$(Date,
#>> '-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, '-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

Re: [sqlite] Date Comparisons SQL

2009-08-03 Thread Igor Tandetnik 
Rick Ratchford wrote:
> The Date is being stored as -mm-dd. Note the "Format$(Date,
> '-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, '-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

Re: [sqlite] Date Comparisons SQL

2009-08-03 Thread Rick Ratchford 
That was a quick response!

The Date is being stored as -mm-dd. Note the "Format$(Date,
'-mm-dd') as Date" that assures this.

And I have it in this format for sDateTemp so that they would compare the
same.

But it does not work.

So what am I doing wrong?

Thanks.

Rick
 
 

#>-Original Message-
#>From: sqlite-users-boun...@sqlite.org 
#>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
#>Sent: Monday, August 03, 2009 1:25 PM
#>To: General Discussion of SQLite Database
#>Subject: Re: [sqlite] Date Comparisons SQL
#>
#>> How do you properly do a Date comparison in SELECT so that the only 
#>> rows returned are those that do not exceed the date found in my 
#>> sDateTemp variable?
#>
#>As a simple string comparison. You made it perfectly right 
#>except that your Date field should be stored in a format 
#>'-mm-dd' in database.
#>Without it comparison will not work.
#>
#>Pavel
#>
#>On Mon, Aug 3, 2009 at 2:19 PM, Rick 
#>Ratchford<r...@amazingaccuracy.com> wrote:
#>> This works:
#>>
#>> SQLString = "SELECT Format$(Date, '-mm-dd') as Date, 
#>Year, Month, 
#>> Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as 
#>> IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as 
#>Delta1, Null 
#>> as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] 
#>GROUP BY 
#>> Year, Month, Day"
#>>
#>>
#>> This does not:
#>>
#>> SQLString = "SELECT Format$(Date, '-mm-dd') as Date, 
#>Year, Month, 
#>> Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as 
#>> IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as 
#>Delta1, Null 
#>> as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] 
#>WHERE Date 
#>> < " & sDateTemp & " GROUP BY Year, Month, Day"
#>>
#>> The difference is that I want the second statement to only retrieve 
#>> records (rows) that do not exceed the date listed in sDateTemp.
#>>
#>> I've converted the local date format to "-mm-dd" in sDateTemp 
#>> before using it in this SQL statement. But that didn't seem 
#>to solve 
#>> the problem. I'm still getting a single blank record.
#>>
#>> How do you properly do a Date comparison in SELECT so that the only 
#>> rows returned are those that do not exceed the date found in my 
#>> sDateTemp variable?
#>>
#>> Thanks.
#>> Rick
#>> ___
#>> 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

Re: [sqlite] Date Comparisons SQL

2009-08-03 Thread Pavel Ivanov 
> How do you properly do a Date comparison in SELECT so that the only rows
> returned are those that do not exceed the date found in my sDateTemp
> variable?

As a simple string comparison. You made it perfectly right except that
your Date field should be stored in a format '-mm-dd' in database.
Without it comparison will not work.

Pavel

On Mon, Aug 3, 2009 at 2:19 PM, Rick Ratchford wrote:
> This works:
>
> SQLString = "SELECT Format$(Date, '-mm-dd') as Date, Year, Month,
> Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as
> IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as Delta1, Null
> as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] GROUP BY
> Year, Month, Day"
>
>
> This does not:
>
> SQLString = "SELECT Format$(Date, '-mm-dd') as Date, Year, Month,
> Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as
> IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as Delta1, Null
> as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] WHERE Date
> < " & sDateTemp & " GROUP BY Year, Month, Day"
>
> The difference is that I want the second statement to only retrieve
> records (rows) that do not exceed the date listed in sDateTemp.
>
> I've converted the local date format to "-mm-dd" in sDateTemp
> before using it in this SQL statement. But that didn't seem to solve
> the problem. I'm still getting a single blank record.
>
> How do you properly do a Date comparison in SELECT so that the only rows
> returned are those that do not exceed the date found in my sDateTemp
> variable?
>
> Thanks.
> Rick
> ___
> 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] Date Comparisons SQL

2009-08-03 Thread Rick Ratchford 
This works:
 
SQLString = "SELECT Format$(Date, '-mm-dd') as Date, Year, Month,
Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as
IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as Delta1, Null
as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] GROUP BY
Year, Month, Day"
 

This does not:
 
SQLString = "SELECT Format$(Date, '-mm-dd') as Date, Year, Month,
Day, Open, High, Low, Close, DayNum, 0 as IsSwingTop1, 0 as
IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as Delta1, Null
as Delta2, 0 as Offset1, 0 as Offset2 FROM [" & sTable & "] WHERE Date
< " & sDateTemp & " GROUP BY Year, Month, Day"
 
The difference is that I want the second statement to only retrieve
records (rows) that do not exceed the date listed in sDateTemp.
 
I've converted the local date format to "-mm-dd" in sDateTemp
before using it in this SQL statement. But that didn't seem to solve
the problem. I'm still getting a single blank record.
 
How do you properly do a Date comparison in SELECT so that the only rows
returned are those that do not exceed the date found in my sDateTemp
variable?
 
Thanks.
Rick
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users