sqlite weirdness

[kyosohma at gmail.com]

Hi,

I am trying to use sqlite to create a local database for an
application I am writing and I am getting some screwy results from it.
Basically, I have a set of values in the database and I am trying to
select a date range and sum those values.

My problem is that it only sums up every other date range and ignores
the in between ranges.

Here's the table structure:

sql = '''CREATE TABLE tbl_TimeEntries (dateworked DATE,
                                  empid INTEGER,
                                  reg REAL,
                                  ot REAL,
                                  ce REAL,
                                  hol REAL,
                                  sklv REAL,
                                  vac REAL,
                                  ct REAL,
                                  conv REAL,
                                  misc REAL,
                                  comments TEXT,
                                  PRIMARY KEY (dateworked, empid))

    '''

I have data in it from 12/18/2006 - 01/26/2007.

I use the following bits of sql:

"SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
'12/17/2006' AND dateworked <= '12/30/2006' AND empid = 281"

"SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
'12/31/2006' AND dateworked <= '01/13/2007' AND empid = 281"

"SELECT SUM(reg), SUM(ot), SUM(ce), SUM(hol), SUM(sklv), SUM(vac),
SUM(ct), SUM(conv), SUM(misc) FROM tbl_TimeEntries WHERE dateworked >=
'01/14/2007' AND dateworked <= '01/27/2007' AND empid = 281"

The first and third return the correct sums. The middle one returns a
list of null values.

If I select everything in the database for that user, I get this:

[(u'12/18/2006', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
u''), (u'12/19/2006', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
u'', u''), (u'12/20/2006', 281, 8.0, u'', u'', u'', u'', u'', u'',
u'', u'', u''), (u'12/21/2006', 281, 8.0, u'', u'', u'', u'', u'',
u'', u'', u'', u''), (u'12/22/2006', 281, 8.0, u'', u'', u'', u'',
u'', u'', u'', u'', u''), (u'12/25/2006', 281, 8.0, u'', u'', u'',
u'', u'', u'', u'', u'', u''), (u'12/26/2006', 281, 8.0, u'', u'',
u'', u'', u'', u'', u'', u'', u''), (u'12/27/2006', 281, 8.0, u'',
u'', u'', u'', u'', u'', u'', u'', u''), (u'12/28/2006', 281, 8.0,
u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'12/29/2006', 281,
8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/01/2007',
281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''),
(u'01/02/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
u''), (u'01/03/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
u'', u''), (u'01/04/2007', 281, 8.0, u'', u'', u'', u'', u'', u'',
u'', u'', u''), (u'01/05/2007', 281, 8.0, u'', u'', u'', u'', u'',
u'', u'', u'', u''), (u'01/08/2007', 281, 8.0, u'', u'', u'', u'',
u'', u'', u'', u'', u''), (u'01/09/2007', 281, 8.0, u'', u'', u'',
u'', u'', u'', u'', u'', u''), (u'01/10/2007', 281, 8.0, u'', u'',
u'', u'', u'', u'', u'', u'', u''), (u'01/11/2007', 281, 8.0, u'',
u'', u'', u'', u'', u'', u'', u'', u''), (u'01/12/2007', 281, 8.0,
u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/15/2007', 281,
8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''), (u'01/16/2007',
281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'', u''),
(u'01/17/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'', u'',
u''), (u'01/18/2007', 281, 8.0, u'', u'', u'', u'', u'', u'', u'',
u'', u''), (u'01/19/2007', 281, 8.0, u'', u'', u'', u'', u'', u'',
u'', u'', u''), (u'01/22/2007', 281, 8.0, u'', u'', u'', u'', u'',
u'', u'', u'', u''), (u'01/23/2007', 281, 8.0, u'', u'', u'', u'',
u'', u'', u'', u'', u''), (u'01/24/2007', 281, 8.0, u'', u'', u'',
u'', u'', u'', u'', u'', u''), (u'01/25/2007', 281, 8.0, u'', u'',
u'', u'', u'', u'', u'', u'', u''), (u'01/26/2007', 281, 8.0, u'',
u'', u'', u'', u'', u'', u'', u'', u'')]
[(80.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0)]

So the data us there, but the sql only works part of the time. My SQL
works if my database is in SQL Server, but not sqlite. Is my SQL
malformed? Is it something about dates in sqlite? Or is it something
else?

Thanks a lot. I apologize in advance for the long post.

Mike