Date format needed to work 'order by date' query in iOS

Question

I am storing list of data in sqlite for my iOS app. In DB one column 'addedOn' stores dates from server response in format '30-Jul-2014 07:43:20'. Here I have tried many things on 'addedOn' column but could not fetch date in ASC/DESC order. Some other details are

1. Column 'addedOn' date type is DATETIME.
2. Queries tried to fetch in DESC used are:

"SELECT * FROM tableName ORDER BY datetime(addedOn) DESC LIMIT 1" & "SELECT * FROM tableName ORDER BY date(addedOn) DESC LIMIT 1" & "SELECT * FROM tableName ORDER BY addedOn DESC LIMIT 1".

But all these queries failed to give me expected result.

3. I tried to convert this date in another format while inserting in DB it self but converting of this date format in other date format like 'yyyy-MM-dd HH:mm:ss' isn't working at all. For that tried below code:

   NSString *dateString = @"30-Jul-2014 07:43:20"; NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init]; [dateFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"]; NSDate *dateFromString = [[NSDate alloc] init]; dateFromString = [dateFormatter dateFromString:dateString];

   NSString *stringDate = [dateFormatter stringFromDate:dateFromString];
   NSLog(@"stringDate %@", stringDate);
   

So my question is this possible to convert this type format in any other format or is there any thing else that I am missing. Please share your thought and suggest me the changes that I need to do. Any help is appreciated.

Answer 1

Your dataFormat seems to be wrong. Try the following DateFormat.

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

NSDate *date = [dateFormatter dateFromString:yourDateString];

This will give you exact date.

Hope it helps..

Answer 2

NSSortDescriptor *sortDescriptor = [[NSSortDescriptor alloc] initWithKey:@"creationDate" ascending:YES];

try NSPredict and use the above code

Answer 3

I have my DB with SQLite & have date in format "dd-mmm-yyyy HH:mm:ss" in which the below query works..

SELECT * FROM TableName ORDER BY addedOn ASC

ASC|DESC can be used as per requirement..

And if you want to convert your date format than do as under :

NSString * dateStr = @"30-Jul-2014 07:43:20";
NSDateFormatter *dateFormat = [[NSDateFormatter alloc]init];
[dateFormat setDateFormat:@"dd-MMM-yyyy HH:mm:ss"];

NSDate *date = [dateFormat dateFromString:dateStr];
[dateFormat setDateFormat:@"yyyy-MM-dd HH:mm:ss"];
NSString *finalString = [dateFormat stringFromDate:date];

here the output is finalString = "2014-07-30 07:43:20"

Answer 4

Thanks folks for your time and suggesting me changes that I need to do. Storing of NSDate as it is in DB instead of Converting NSDate in NSString and then inserting in DB solved my problem with little change in code

There are two cases in which my code works now

NSString *dateString = MD_history.addedOn;
NSDateFormatter *df = [[NSDateFormatter alloc] init];
[df setDateFormat:@"dd-MMM-yyyy HH:mm:ss"];
NSDate *dateFromString = [[NSDate alloc] init];
dateFromString = [df dateFromString:dateString];

And

NSString *dateString = MD_history.addedOn;
NSDateFormatter *df = [[NSDateFormatter alloc] init];
NSLocale *usLocale = [[NSLocale alloc] initWithLocaleIdentifier:@"en_US"];
[df setLocale:usLocale];
[df setDateFormat:@"dd-MMM-yyyy HH:mm:ss"];
NSDate *dateFromString = [[NSDate alloc] init];
dateFromString = [df dateFromString:dateString];

In this case Date after inserting in DB is in "2014-07-30 02:13:20 +0000" format. In this sorting of date column works proper.