SQLite date fields

[MRAB]

On 25/11/2010 00:45, Alan Harris-Reid wrote:
>
> Hi,
>
> I am having design problems with date storage/retrieval using Python and
> SQLite.
>
> I understand that a SQLite date column stores dates as text in ISO
> format (ie. '2010-05-25'). So when I display a British date (eg. on a
> web-page) I convert the date using
> datetime.datetime.strptime(mydate,'%Y-%m-%d').strftime('%d/%m/%Y').
>
> However, when it comes to writing-back data to the table, SQLite is very
> forgiving and is quite happy to store '25/06/2003' in a date field, but
> this is not ideal because a) I could be left with a mixture of date
> formats in the same column, b) SQLite's date functions only work with
> ISO format.
> Therefore I need to convert the date string back to ISO format before
> committing, but then I would need a generic function which checks data
> about to be written in all date fields and converts to ISO if necessary.
> That sounds a bit tedious to me, but maybe it is inevitable.
>
> Are there simpler solutions? Would it be easier to change the date field
> to a 10-character field and store 'dd/mm/yyyy' throughout the table?
> This way no conversion is required when reading or writing from the
> table, and I could use datetime() functions if I needed to perform any
> date-arithmetic.
>
> How have other developers overcome this problem? Any help would be
> appreciated. For the record, I am using SQLite3 with Python 3.1.
>
Even when storing a date as text, I always prefer to use ISO format
because it makes sorting by date easier and there's less chance of
confusion over UK vs US date format.