CODEWITHSUNDEEP
sqlselectsqlite
Cocoa Dev
Cocoa Dev

Reputation: 9561

SQLQuery to find dates in a specified range

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

Answers (1)

Joe Stefanelli
Joe Stefanelli

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

Related Questions