Re: [sqlite] Query Doesn't Find Record
Kees Nuyt wrote: > >>What are you folks using to type these queries? I've yet to find any good >>utilities that do this for the Windows platform. > > sqlite3.exe in a CMD window. > > And SQLiteSPy for browsing databases. > Thanks! Jonathan -- View this message in context: http://www.nabble.com/Query-Doesn%27t-Find-Record-tp22469520p22505383.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Doesn't Find Record
On Fri, 13 Mar 2009 12:42:46 -0700 (PDT), jonwood wrote: > > >P Kishor-3 wrote: >> >> why don't you try it? See below -- >> > >What are you folks using to type these queries? I've yet to find any good >utilities that do this for the Windows platform. sqlite3.exe in a CMD window. If I suspect the SQL is difficult enough to make typoos, I write a script with an ascii editor. sqlite3 test.db3 Thanks. > >Jonathan -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Doesn't Find Record
P Kishor-3 wrote: > > why don't you try it? See below -- > What are you folks using to type these queries? I've yet to find any good utilities that do this for the Windows platform. Thanks. Jonathan -- View this message in context: http://www.nabble.com/Query-Doesn%27t-Find-Record-tp22469520p22501454.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Doesn't Find Record
On Wed, Mar 11, 2009 at 11:08 PM, jonwood wrote:
>
>
> Doug Currie-2 wrote:
>>
>> Note the '/'s
>>
>
> What does this mean? What does DATE('2009-1-1') or DATE('2009/1/1') return?
> Does DATE() simply have no effect whatsoever?
> --
why don't you try it? See below --
[07:42 PM] ~/Projects$sqlite3
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE foo (a DATETIME);
sqlite> INSERT INTO foo VALUES ('2009-03-11');
sqlite> INSERT INTO foo VALUES ('2009/03/11');
sqlite> SELECT * FROM foo;
2009-03-11
2009/03/11
sqlite> SELECT date(a) FROM foo;
2009-03-11
sqlite>
Dates are stored as strings in SQLite, but the string has to be
formatted as -mm-dd hh:mm:ss. You can't use any old delimiter. If
you use the correct delimiter, then the built in function date()
allows you to do magic stuff with dates.
--
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Doesn't Find Record
On Mar 12, 2009, at 12:08 AM, jonwood wrote:
> Doug Currie-2 wrote:
>>
>> Note the '/'s
>>
>
> What does this mean? What does DATE('2009-1-1') or DATE('2009/1/1')
> return?
> Does DATE() simply have no effect whatsoever?
Sorry to be cryptic.
sqlite> select date('2009/12/03');
sqlite> select date('2009-12-03');
2009-12-03
sqlite>
The '/'s format is not supported by DATE.
See: http://www.sqlite.org/lang_datefunc.html
e
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Doesn't Find Record
Doug Currie-2 wrote:
>
> Note the '/'s
>
What does this mean? What does DATE('2009-1-1') or DATE('2009/1/1') return?
Does DATE() simply have no effect whatsoever?
--
View this message in context:
http://www.nabble.com/Query-Doesn%27t-Find-Record-tp22469520p22469578.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Doesn't Find Record
On Mar 12, 2009, at 12:01 AM, jonwood wrote:
> PaymentDate=2009/01/05
Note the '/'s
> And then I ran the following query:
>
> SELECT * FROM Payments WHERE FK_CustomerID=5 AND DATE(PaymentDate) >=
> DATE('2009-01-01') AND DATE(PaymentDate) <= DATE('2009-03-11')
Note the '-'s.
'2009/' > '2009-'
e
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query Doesn't Find Record
Okay, so SQLite stores dates as strings, for whatever reason. I thought I had
this figured out but now I'm running into the following problem:
I have a table called Payments that contains a single row of data with the
following values:
PaymentID=1
FK_CustomerID=5
PaymentDate=2009/01/05
PaymentType=Check
PaymentNumber=123456
PaymentDescription=654321
PaymentTotal=400
And then I ran the following query:
SELECT * FROM Payments WHERE FK_CustomerID=5 AND DATE(PaymentDate) >=
DATE('2009-01-01') AND DATE(PaymentDate) <= DATE('2009-03-11')
The query returns no rows. I'm at a complete loss here. Can anyone tell me
what I'm missing?
Thanks.
--
View this message in context:
http://www.nabble.com/Query-Doesn%27t-Find-Record-tp22469520p22469520.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
