SQLite date fields

[Alan Harris-Reid]

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.

Alan