Convert integer column to date column using sqlite query

Question

I am using SQLite. In my database, I have a integer column value like 20050819. I would like to convert this as date column as like 2005-08-19.

I can achieve my requirement when using the function CONVERT(columnName, DATETIME) in MySQL server. But I need the same result in SQLite.

Is this possible in SQLite query?

Answer 1

You basically have to break apart the date:

select printf('%4d-%02.2d-%02.2d',
              columnname/10000, columnname%10000/100, columnname%100)
from tablename;

P.S. If you have the choice, you will be far better off storing the date in ISO standard format ('YYYY-MM-DD') in the database in the first place.