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 -- http://mail.python.org/mailman/listinfo/python-list
