Reputation: 9561
Here is my Query for an SQLite3 DB
SELECT tddate,
CASE tdtype
WHEN 'LOGIN' THEN MIN(tdtime)
WHEN 'SHUTDOWN' THEN MAX(tdtime)
ELSE NULL
END AS tdtime, tdtype, tdusername
FROM TimeData
WHERE tdusername LIKE 'joe.shmoe'
AND tddivision = 'divisionA'
AND tddate > '6/1/2011'
AND tddate < '7/5/2011'
GROUP BY tddate, tdtype, tdusername
ORDER BY tddate, tdtime DESC
results are dates that are from 6/30/2011 to 7/11/2011
I tried another query (much simplier to isolate the problem). I get the same results.
SELECT *
FROM TimeData
WHERE tddate > '6/1/2011'
AND tddate < '7/5/2011'
ORDER BY tddate DESC
Here is the DDL for the table
CREATE TABLE [TimeData] (
[tdtag] CHAR(8),
[tdusername] CHAR(50),
[tddivision] CHAR(50),
[tddate] CHAR(10),
[tdtime] CHAR(8),
[tdipaddress] CHAR(14),
[tdtype] CHAR(8));
Upvotes: 0
Views: 84
Reputation: 135938
SQLite doesn't have a true "date" data type and it's very picky about the format of date strings. Try specifying your dates in the format: YYYY-MM-DD
. For more details, see the documentation here.
Upvotes: 1