Re: [sqlite] Query by Day

[John Machin]

On 8/07/2009 2:14 AM, Rick Ratchford wrote:
[snip]

> To John Machin: To save from answering multiple messages (and save space for
> all), I'll address John's reply here.
> 
> --
> "Consider getting answers faster by (a) trying things out yourself, e.g. 
> type this at the sqlite3 command-line program:
> 
> select CAST(strftime('%d', '2009-06-30'), INTEGER);
> 
> and (b) looking at the docs; in this case
> http://www.sqlite.org/lang_expr.html;
> --
> 
> I'll consider myself admonished. But let me say that I go through a lot of
> searching and testing before I ask questions. Also, I'm a novice programmer
> and really new to SQLite. So some things may be more obvious to others than
> to me. I'm not yet familiar with a "sqlite3 command-line program".

It's a very handy utility ... trying syntax out to see if it works; 
prototyping queries before you embed them in VB6 or whatever; lots more 
uses. In future when you have a question like "why is my query producing 
output X instead of output Y" you will be helping yourself more if you 
publish the query and say "I've run this using the sqlite3 program and 
it gives the same unexpected results" [you now have a larger audience; 
people who don't know any VB6 can help you] or "it gives the expected 
results" [you've isolated the problem to be related to usage of VB6; 
you'll get more focussed responses]

docs: http://www.sqlite.org/sqlite.html

download: http://www.sqlite.org/download.html
See "Precompiled Binaries For Windows ... sqlite-3_6_16.zip (246.32 
KiB) ... A command-line program for accessing and modifying SQLite 
databases."

> I'm
> programming in VB6. My question on CAST wasn't so much whether I can do it
> (as testing would bear out), but was more towards whether I should do it.

If you mean "should", say "should", not "can".

> Experts here could steer me away towards a better way that I'm not aware of.

> Anyway, thank you John.

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

Re: [sqlite] Query by Day

[Rick Ratchford]

Thank you Dennis. 

That does make sense. In my particular situation, I'd have to typecast the
variable fed to the SQL, since it is an INT type that holds the day that is
to be searched on.

What I ended up doing, for purposes of time and because there are other
routines that will need to do this as well, is that when I created the
table, I added an additional column of type INT with the day value already
there. So now I'll just do a search on that column and be done with it. But
thanks though, because I wish to learn what is available to me using SQLite
with my VB6.

To John Machin: To save from answering multiple messages (and save space for
all), I'll address John's reply here.

--
"Consider getting answers faster by (a) trying things out yourself, e.g. 
type this at the sqlite3 command-line program:

select CAST(strftime('%d', '2009-06-30'), INTEGER);

and (b) looking at the docs; in this case
http://www.sqlite.org/lang_expr.html;
--

I'll consider myself admonished. But let me say that I go through a lot of
searching and testing before I ask questions. Also, I'm a novice programmer
and really new to SQLite. So some things may be more obvious to others than
to me. I'm not yet familiar with a "sqlite3 command-line program". I'm
programming in VB6. My question on CAST wasn't so much whether I can do it
(as testing would bear out), but was more towards whether I should do it.
Experts here could steer me away towards a better way that I'm not aware of.

Anyway, thank you John.

Regards,
Rick
 

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dennis Cote
Sent: Tuesday, July 07, 2009 8:22 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query by Day

Rick Ratchford wrote:
> So what I need to do then is to make the return of strftime of type INT.
>
> Can CAST(strftime('%d', Date), INTEGER) be used in this context, or is 
> there another way?
>
>   
Rick,

You could use a cast (with correct syntax) as you have suggested

CAST(strftime('%d', Date) AS INTEGER)

Or you could simply provide the day you are checking for as a string. To do
this, surround the number with single quotes to turn it into a string
literal.

SQLString = "SELECT strftime('%d', Date) as Day, IsSwingTop1 as Tops,
IsSwingBtm1 as Btms " & _
"FROM TmpTable WHERE Day = '11'"


HTH
Dennis Cote
___
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] Query by Day

[Dennis Cote]

Rick Ratchford wrote:
> So what I need to do then is to make the return of strftime of type INT.
>
> Can CAST(strftime('%d', Date), INTEGER) be used in this context, or is there
> another way?
>
>   
Rick,

You could use a cast (with correct syntax) as you have suggested

CAST(strftime('%d', Date) AS INTEGER)

Or you could simply provide the day you are checking for as a string. To 
do this, surround the number with single quotes to turn it into a string 
literal.

SQLString = "SELECT strftime('%d', Date) as Day, IsSwingTop1 as Tops,
IsSwingBtm1 as Btms " & _
"FROM TmpTable WHERE Day = '11'"


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

Re: [sqlite] Query by Day

[John Machin]

On 7/07/2009 10:13 AM, Rick Ratchford wrote:
> Hi Simon. 
> 
> Ah. So what I need to do then is to make the return of strftime of type INT.
> 
> Since I'm creating a recordset from an existing table (rather than creating
> a table itself), then I don't have the option to set the affinity of my
> newly created column Day to INT.
> 
> Can CAST(strftime('%d', Date), INTEGER) be used in this context, or is there
> another way?

It can be used, but not with a very productive outcome.

Consider getting answers faster by (a) trying things out yourself, e.g. 
type this at the sqlite3 command-line program:

select CAST(strftime('%d', '2009-06-30'), INTEGER);

and (b) looking at the docs; in this case 
http://www.sqlite.org/lang_expr.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query by Day

[Rick Ratchford]

Hi Simon. 

Ah. So what I need to do then is to make the return of strftime of type INT.

Since I'm creating a recordset from an existing table (rather than creating
a table itself), then I don't have the option to set the affinity of my
newly created column Day to INT.

Can CAST(strftime('%d', Date), INTEGER) be used in this context, or is there
another way?

Thanks!


Rick
 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Davies
Sent: Monday, July 06, 2009 4:44 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query by Day

2009/7/6 Rick Ratchford <r...@amazingaccuracy.com>:
> Greetings!
>

Hi Rick,

> I'm having trouble with what I thought would be a simple SQL query.
>
>    SQLString = "SELECT strftime('%d', Date) as Day, IsSwingTop1 as 
> Tops,
> IsSwingBtm1 as Btms " & _
>                "FROM TmpTable WHERE Day = 11"
>
> I'm trying to create a recordset where the only records returned are 
> those that fall on the 11th of the month.
>
> When I run this without the 'WHERE Day = 11", it returns all the 
> records in
> 3 columns, Day, IsSwingTop1 and IsSwingBtm1. Under the Day column, I 
> can see all the days as 01, 02...31.

That's good...

>
> I'm not sure where I'm making my logic error.

...and you think that there is a problem because?

It would really help if you explained what leads you to believe that there
is a logic error.

>
> Thanks.
>
> Rick
>

If you use typeof() around "strftime('%d',date)", it shows type TEXT.
You are comparing this with an INTEGER; never true.

Regards,
Simon
___
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] Query by Day

[Simon Davies]

2009/7/6 Rick Ratchford :
> Greetings!
>

Hi Rick,

> I'm having trouble with what I thought would be a simple SQL query.
>
>    SQLString = "SELECT strftime('%d', Date) as Day, IsSwingTop1 as Tops,
> IsSwingBtm1 as Btms " & _
>                "FROM TmpTable WHERE Day = 11"
>
> I'm trying to create a recordset where the only records returned are those
> that fall on the 11th of the month.
>
> When I run this without the 'WHERE Day = 11", it returns all the records in
> 3 columns, Day, IsSwingTop1 and IsSwingBtm1. Under the Day column, I can see
> all the days as 01, 02...31.

That's good...

>
> I'm not sure where I'm making my logic error.

...and you think that there is a problem because?

It would really help if you explained what leads you to believe that
there is a logic error.

>
> Thanks.
>
> Rick
>

If you use typeof() around "strftime('%d',date)", it shows type TEXT.
You are comparing this with an INTEGER; never true.

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