SOLite: date formatting in SQLite

Question

I am starting with SQLite. I need to insert as well as receive data in DD/MM/YYYY format. As in oracle, there are TO_DATE() , TO_CHAR() functions similarly whether there are any date formatting functions in SQLite.

I want my date to be in DD / MM / YYYY format. Can anyone help me?

Thanks.

Answer 1

Sqlite does not have a datetime data type, so it does not have any TO_DATE and TO_CHAR . Dates are stored as strings.

some function for processing the date and time that ISO-8601 uses, since the lexicographic sorting in this format corresponds to the chronological sorting, which does not apply to your format, therefore, if you want to order by dates, you must use the iso format. I also suggest you use the iso format because it is not ambiguous, while slashes are used both in the DD / MM / YYYY format and in the MM / DD / YYYY format, which leads to confusion quite quickly.

Please note that functions can convert the ISO format to any format you want, so you can have your own format for reading, but there seems to be no inverse function, so you need to convert the ISO format in your application code.

Answer 2

I'm not sure if this is what you want, but generally speaking, you can just add the date in string format to the table and get it as a string. But the string must be in the date format supported by SQLite, "yyyy-MM-dd HH: mm: ss" or "yyyy-MM-dd".

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