SOLite:Date formatter in SQLite

Question

I am a beginner to SQLite. I have to insert as well as retrieve data in DD/MM/YYYY format. As in Oracle there are function TO_DATE(),TO_CHAR() similarly, are there any date formatter functions in SQLite.

I want my date in DD/MM/YYYY format.

Answer 1

Sqlite does not have a datetime datatype, so it does not have any TO_DATE and TO_CHAR functions either. Dates are stored as strings.

There are some function for date and time manipulation, which use the ISO-8601 format, because in that format lexicographical sort corresponds to chronological sort, which is not the case of your format, so if you want to order by the dates, you have to be using the iso format. I'd also suggest you use the iso format because it's not ambiguous, while slashes are used in both DD/MM/YYYY format and MM/DD/YYYY format leading to confusion pretty quickly.

Note, that the functions can convert the ISO format to whatever format you want, so for reading, you can have your format, but there does not seem to be the inverse function, so converting to ISO format would have to be done in your application code.

Answer 2

I am not sure this is what you want, but, generally speaking, you can just add the date in string format to the table and retrieve it as string itself. But the string should be in the SQLite supported date format "yyyy-MM-dd HH:mm:ss", or "yyyy-MM-dd".

You can convert a date-to-string or string-from-date using NSDateFormatter. So, after retrieving the date from db, you can convert it to "DD/MM/YYYY" format using NSDateFormatter.