CODEWITHSUNDEEP
androiddatabasesqlitedatetime
Arslan Anwar
Arslan Anwar

Reputation: 18746

SQLlite query for getting data BETWEEN 2 dates not working

I am using date sqlite in android and want to filter the date between two dates. I don't what the hell is wrong with my query? Its not giving error , but also not giving result. I think I am doing something wrong with BETWEEN date AND date function 

SELECT * FROM trip_mark WHERE file_type = 2 
AND create_time BETWEEN date('2011-11-08 00:00:00') AND date('2011-11-10 00:00:00')

Below is my table structure. I know there is date in my table, I am using SQLite browser and check the data in DB. 

CREATE TABLE `trip_mark` (
`file_id` INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL,
`local_path` varchar(100) DEFAULT NULL ,
`file_type` int(11) DEFAULT NULL,
`import_date` datetime DEFAULT NULL ,
`create_time` datetime DEFAULT NULL ,
`isSyncAvailable` int(11) DEFAULT '1' ,
`syncDate` datetime DEFAULT NULL ,
`isSynced` int(11) DEFAULT '0' ,
`longitude` float DEFAULT NULL ,
`altitude` float DEFAULT NULL ,
`place_logical_name` varchar(150) DEFAULT NULL ,
`file_note` varchar(250) DEFAULT NULL )

Here is the table current data

"1","/sdcard/TripMark_1323375027.jpg","2","2011-11-09 01:10:37","2011-11-09 01:10:37","1","null","0","-1","-1","Unknown Location","");
"2","/sdcard/TripMark_1323375053.jpg","2","2011-02-09 01:11:01","2011-02-09 01:11:01","1","null","0","-1","-1","Unknown Location","");
"3","/sdcard/TripMark_1323375260.jpg","2","2011-11-09 01:14:29","2011-11-09 01:14:29","1","null","0","-1","-1","Unknown Location","sdfas");

I want to run this query 

SELECT * FROM trip_mark WHERE file_type = 2 
AND create_time BETWEEN date('2011-11-08 00:00:00') AND date('2011-11-10 00:00:00')

and the expected output data is 

"1","/sdcard/TripMark_1323375027.jpg","2","2011-11-09 01:10:37","2011-11-09 01:10:37","1","null","0","-1","-1","Unknown Location",""
"3","/sdcard/TripMark_1323375260.jpg","2","2011-11-09 01:14:29","2011-11-09 01:14:29","1","null","0","-1","-1","Unknown Location","sdfas"

Upvotes: 1

Views: 6708

Answers (4)

Ankita Singh
Ankita Singh

Reputation: 314

SELECT * FROM transactionList WHERE transactionDate BETWEEN '2018-04-29' AND '2018-05-29' LIMIT 20;

try this query and it will work like wow :)

Please check the question

Filter Not Working on date

Upvotes: 0

Muhammad Nabeel Arif
Muhammad Nabeel Arif

Reputation: 19310

I experienced the same problem. What I found that date function is causing problem. As the String in ' ' is already a date. I remove date function from the query and it worked for me. You can also try this query.

SELECT * FROM trip_mark WHERE file_type = 2 
AND create_time BETWEEN '2011-11-08 00:00:00' AND '2011-11-10 00:00:00'

Upvotes: 2

Bertrand Kintanar
Bertrand Kintanar

Reputation: 132

The fact that you're query didn't show any error it means it is correct. Make sure thought that there's a record in your database with file_type = 2.

Edit: Given your sample data. You are storing string on your file_type. Remove the quotation marks on the file_type.

Upvotes: 0

Caner
Caner

Reputation: 59168

SQLite doesn't have datetime,float,int,varchar data types. See a list of available types here:
http://www.sqlite.org/datatype3.html

Upvotes: 3

Related Questions