1

Change this date format which is in sqlite db 12/10/11 to 12-10-11 (mm-dd-yy) I am unable to do so .I am a noob in sqlite and have to parse this value SELECT strftime('%d-%m-%Y',Date) from report but I am getting null as sqlite db excepts value in mm-dd-yy so How do I convert format 12/10/11 to 12-10-11 (mm-dd-yy) .Thanks in advance .Really appreciate the help.

Jacob Wood
  • 467
  • 9
  • 26
  • 2
    From your description, it sounds like the data is not _stored_ properly in the first place. In that case, to convert it you must either do it yourself, or store it in the format SQLite recognizes as a date that it can format. – mah Dec 24 '12 at 12:48
  • Can you try 'Date', i think Date must has ''. Take enclosed in quotation marks – hakki Dec 24 '12 at 12:49
  • Look [here](http://www.sqlite.org/datatype3.html) for "Date and Time Datatype". Basically, SQLite doesn't have a date data type, but has some preferred formats. If you have a date stored in a different format than the preferred ones then you must select on a "raw" value basis rather than using the built-in date/time functions. – Hot Licks Dec 24 '12 at 14:55

1 Answers1

1

The short answer:

If you have a text string stored as "12/10/11" that you want reported as "12-10-11", you should use the replace(X,Y,Z) function, to replace occurrences of Y in X with Z. Thus:

SELECT replace('12/24/11','/','-');

will return:

12-10-11

The long answer:

First, dates do not actually exist as a proper datatype in SQLite. They're stored as either TEXT, REAL, or INTEGER values. See date and time datatype in SQLite. So it depends upon how your date was stored in the database.

Second, you seem to be implying that you stored the date in a "mm/dd/yy" format. That's not a valid/useful TEXT format to be storing date/time values (as the date cannot be sorted, cannot used in "greater than" and "less than" operations, cannot be used in SQLite date functions, etc.). You really want to store datetime values in one of the formats listed in the "Time strings" section of the date and time functions document.

So, generally you should store your date/time values in one of those formats, use NSDateFormatter to convert that to a NSDate when you retrieve it from the database. And when you want to display the date value in your app, use whatever format you want for output.

But, if you don't care that the dates are stored as text strings and are not effectively usable as dates in SQLite, then just treat it as a plain old TEXT string and use TEXT functions, such as replace(X,Y,Z) to replace occurrences of "/" with "-", as outlined above.

Rob
  • 415,655
  • 72
  • 787
  • 1,044
  • Thanks Rob.I have another addon query please help me out . I have used this query **SELECT strftime('%Y', replace(Date,'/','-')) from report** .I am trying to achieve this - http://stackoverflow.com/questions/14019624/sqlite-query-to-find-sum-of-a-year-by-the-given-dates – Jacob Wood Dec 24 '12 at 17:20
  • I don't think you can use those date functions unless the date field is in one of those formats listed in that doc I referenced in my reply. Notably, e.g. it would need to be in `YYYY-MM-DD` format (note four character year, too). Alternatively, if your dates are not in a valid date format, you could grab the two digit year from your `MM-DD-YY` format by using the simple `substr(date, 7, 2)`. – Rob Dec 24 '12 at 17:37