CODEWITHSUNDEEP
iosiphoneobjective-csqlite
Chandu
Chandu

Reputation: 695

sqlite query for retrieving data between two dates saved as strings

 NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO ADDEXPENSE(date, description, category, amount) VALUES (\"%@\", \"%@\", \"%@\",\"%@\")", fieldOne.text, fieldTwo.text,fieldThree.text , fieldFour.text];

i have taken a date picker for the fieldOne and saving data is working fine. but how to retreive the data between two dates?

and this is the the pickerview i used

UIDatePicker *picker = (UIDatePicker *)sender;
NSDate *dateSelected = [picker date];
NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
[dateFormatter setDateStyle:NSDateFormatterMediumStyle];

Upvotes: 1

Views: 3168

Answers (5)

Randika Vishman
Randika Vishman

Reputation: 8124

Following was regarding to an iOS App which I was doing, which uses SQLite DB.

SELECT COUNT(carSold) 
FROM cars_sales_tbl
WHERE date
BETWEEN '2015-04-01' AND '2015-04-30'
AND carType = "Hybrid"

It works like a charm! Hope someone will get use out of it!

Cheers!

Upvotes: 0

Nilesh_iOSDev
Nilesh_iOSDev

Reputation: 671

I also stuck with same problem. I have to fetch data between two dates. but I am storing date as string in SQLite in 'YYYY-MM-dd' this format. then i use below query to fetch data. & I got Result. In this I just typecast my date column to Date type. 

SELECT * FROM tableName WHERE columnnName(Date) BETWEEN 'YYYY-MM-dd' and 'YYYY-MM-dd'

Upvotes: 1

Alan Moore
Alan Moore

Reputation: 6575

You can build a sqlite query to select rows in a date range. First you must format in sql friendly format like so:

NSDate * selected = [datePicker date];
NSDateFormatter *dateFormat = [[NSDateFormatter alloc] init];
[dateFormat setDateFormat:@"yyyy-MM-dd"];
NSString * dateString = [dateFormat stringFromDate:selected];
NSString * sql = [[NSString alloc] initWithFormat:@"SELECT * FROM table WHERE (startdate <= '%@') AND (enddate >= '%@)", dateString, dateString];

and so forth. You need to format your dates properly when inserting them into your table.

Upvotes: 1

Serhii Mamontov
Serhii Mamontov

Reputation: 4932

I suggest you to read this Date and Time Functions on SQLite website. You can adapt those functions to retrieve unix-timestamp from format saved by NSDate and than simply use them in your SQL query in WHERE clause.

Upvotes: 1

Tendulkar
Tendulkar

Reputation: 5540

first you have to retrieve the dates and convert into the nsdates.Then you have to check the each row date (whether it is between the two mentioned dates or not) using the compare function in dates.

Upvotes: 0

Related Questions