SQLite date fields

Tim Roberts timr at probo.com
Fri Nov 26 07:45:05 CET 2010

Alan Harris-Reid <alan at baselinedata.co.uk> wrote:
>I am having design problems with date storage/retrieval using Python and 
>I understand that a SQLite date column stores dates as text in ISO 
>format (ie. '2010-05-25'). 

Only if you store it that way.

>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,

SQLite is essentially typeless.  ALL fields are stored as strings, with no
interpretation.  You can store whatever you want in any column.  The column
types are basically there to remind YOU how to handle the data.

>How have other developers overcome this problem?

By converting.  SQLite's flexibility is cool, but it means you have to be
smart about how you store the data.
Tim Roberts, timr at probo.com
Providenza & Boekelheide, Inc.

More information about the Python-list mailing list