CODEWITHSUNDEEP
iosiphonexcodesqlitedate
Devang Goswami
Devang Goswami

Reputation: 284

select data for particular month and year sqlite xcode

Having some problems to retrive data from sqlite hope you guyz can help me out ,

So , Let me describe my problem here ,

CREATE TABLE ex_manager (ex_id integer PRIMARY KEY AUTOINCREMENT,ex_title text,ex_amount text,ex_description text,ex_category text,ex_date DATE ,ex_upload_date DATE, ex_image text)

This is the query used to create a table ,

Table have date feild ex_date with DATE datatype ,

Now , i insert date with format yyyy-MM-dd like ,

NSDate *today=[NSDate date];
    NSDateFormatter *df=[[NSDateFormatter alloc] init];
    [df setDateFormat:@"dd-MM-yyyy"];
    NSString *datestring = [df stringFromDate:today];

and the query i execute is:

query= [NSString stringWithFormat:@"insert into ex_manager1 (ex_title,ex_amount,ex_description,ex_date,ex_category,ex_upload_date,ex_image) values ('%@','%@','%@','%@','%@','%@','%@')",_ex_title.text,_ex_amount.text,@"abcd",_ex_date.text,_ex_category.text,datestring,path ];

And , I can successfully able to add data to the database ,

and i want to retrive data for particular month and year .

I run the query like

SELECT * FROM ex_manager where strftime('%Y%m',ex_date) = '201502'

and no data are there in result .

Can anyone please guide me how should i create db and insert value and how to retrive data for particular month and year by selecting and passing it in query .

Thanks in Advance . and it will be very helpful for me ,please help me out .

Upvotes: 0

Views: 677

Answers (2)

CL.
CL.

Reputation: 180210

dd-MM-yyyy is not one of the supported date formats for time strings, so the return value of strftime() is always NULL.

Use yyyy-MM-dd instead.
(In that case, you can also avoid calling the strftime function and use WHERE ex_date GLOB '2015-02*' instead, which would allow the database to use an index for the search.)

Upvotes: 1

Dipen Chudasama
Dipen Chudasama

Reputation: 3093

I think may be there was an issue with inserting data, try to replace this line may be help you.

query= [NSString stringWithFormat:@"insert into ex_manager1 (ex_title,ex_amount,ex_description,ex_date,ex_category,ex_upload_date,ex_image) values (\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\")",_ex_title.text,_ex_amount.text,@"abcd",_ex_date.text,_ex_category.text,datestring,path ];

Upvotes: 0

Related Questions