Retrieve month part from date saved in sqlite data

问题:

hope every one is doing well :)

I have struck with retrieving the month from the date that is saved in sqlite database.First of all,I would like to thank some of the experts who guided me for proper insertion of a date in to sqlite database table i.e of the following format:

[dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"];

I have displayed all the data including date in a controller page say "View Reminder" page.

Now I have a view controller for the user to view month wise,say if the user selects month "January" from picker view it will navigate to view controller where I display the reminders corresponding to the selected month.

I have earlier got struck at how to access the string value selected from picker view,so that we can use the query:

"SELECT * FROM reminders WHERE Grp = '%@'",appDelegate.groupString

Where groupString is declared in app delegate file,assigned a value in view controller where picker view is present and accessed that string using the above query in display view

which was the solution I found out here .Now I am trying to do the same thing for retrieving dates,i.e. simply write the query as:

"SELECT * FROM reminders WHERE Date = '%@'",appDelegate.monthString

Its not working because the date is saved in @"yyyy-MM-dd HH:mm:ss".So I have made my research on this through various links,I experimented the following ways:

"SELECT * FROM reminders WHERE MONTH like 'January %'" etc..

but as I have mentioned the string selected must be referenced to the value,hence used:

MONTH like '%@',appDelegate.monthString

"SELECT * FROM reminders WHERE strftime('%m', Date) = '%@'",appDelegate.monthString

Gone through sqlite date data types and This Link

None of them worked

As we all know we have a straight forward query for retrieving all the reminders from a specific month:

"SELECT * FROM reminders WHERE Date BETWEEN '2012-01-01' AND '2012-01-31'"

It works perfect,but I need to reference the January, February,March (selected from picker view) etc.. as reference string to date,I am not aware of how to get month part from date.Is there any query for retrieving the month part from a date say 2012-01-01 as January or some other in that particular context....

Please help me,Thanks all in advance :)

EDIT:

I have changed the display format in to just month followed by date i.e.:

ReminderClass *remin = [[ReminderClass alloc]init];
remin.Date = [[[NSString alloc]initWithUTF8String:(const char *)sqlite3_column_text(statament, 3)]autorelease];

                NSDateFormatter *dateFormat = [[[NSDateFormatter alloc]init]autorelease];
                [dateFormat setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
                NSDate *date = [dateFormat dateFromString:remin.Date];
                [dateFormat setDateFormat:@"MMMM dd"];
                NSString *dateVal = [dateFormat stringFromDate:date];
                remin.Date = dateVal;

But before we do that we need to write the sql query for retrieving data,i.e.:

sqlQuery = [NSString stringWithFormat:@"select * from reminders WHERE ????",appDelegate.monthString];

The question mark is what I need,thanks once again :)

回答1:

you can use dateFormat for retrieving month from date:

NSDate *today = [NSDate date];

NSDateFormatter *monthFormat = [[NSDateFormatter alloc] init];
[monthFormat setDateFormat:@"MM"];

回答2:

First of all we need to form a date manually.To do that we need to create NSDateComponents consisting of the required components in the following way:

NSDateComponents *components = [[NSCalendar currentCalendar] components:NSDayCalendarUnit | NSMonthCalendarUnit | NSYearCalendarUnit fromDate:[NSDate date]];

-> Get the current year or present year using the components i.e:

NSInteger year = [components year];

-> Form the 3 strings year,month,day manually to get From date:

NSString *fromString = [NSString stringWithFormat:@"%d",year];
NSString *string2 = [NSString stringWithFormat:@"-%@",appDelegate.monthValue];
NSString *string3 = @"-01";

appDelegate.monthValue is nothing but the string assigned in appDelegate and assigned with the value selected in pickerView which contains 01,02,03.....12 month values

As we know 01 is nothing but the day(min range) in any month,we can simply hard-code it:)

Now we are ready to form the From Date,here we go:

string2 = [string2 stringByAppendingString:string3];
fromString = [fromString stringByAppendingString:string2];
NSDateFormatter *dateFormatter = [[[NSDateFormatter alloc] init]autorelease];
[dateFormatter setDateFormat:@"yyyy-MM-dd"];

NSString *dateString=[NSString stringWithString:fromString];

NSDate *fromDate = [dateFormatter dateFromString:dateString];

NSString* finalFromDateString = [dateFormatter stringFromDate:fromDate];

Please observe the additional change that I have made to form the date,used 2 strings because we will have a problem when assigning string to NSDate,(improper date retrieval i.e. one day less than the actual date). For more details please follow this link . Now we are finished with from date.

-> Get the to date using month as reference

To get to date or max range in a month,we need to use NSRange method which will calculate the range for a month or number of days for a month in that particular year,here it is:

NSString *toString = [NSString stringWithFormat:@"%d",year];

NSString *monthValue = [NSString stringWithFormat:@"-%@",appDelegate.monthValue];

NSRange monthRange = [gregorian rangeOfUnit:NSDayCalendarUnit inUnit:NSMonthCalendarUnit forDate:fromDate];

NSString *rangeString = [NSString stringWithFormat:@"-%d",monthRange.length];

monthValue = [monthValue stringByAppendingString:rangeString];

toString = [toString stringByAppendingString:monthValue];

NSDate *toDate = [dateFormatter dateFromString:toString];

NSString *finalToDateString = [dateFormatter stringFromDate:toDate];

We are done with From date and To date now form the query using the following statement:

NSString *sqlQuery = [NSString stringWithFormat:@"select * from reminders WHERE Date BETWEEN '%@' AND '%@'",finalFromDateString,finalToDateString];

Thats it,now we must be able to retrieve all the data corresponding to a particular month or the month that is selected in picker view

Thank you all,have a nice time :)