1

I have a column of data type TEXT:

date
----
DD/MM/YYYY

but I want to convert all rows in the column to:

date
----
YYYY-MM-DD 00:00:00

(Yes, 00:00:00 for all rows)

Is there any way to do it in SQLite?

1 Answers1

0

Use strftime.

strftime('%Y-%m-%d %H:%M:%S', date_str);

EDIT: Yes, my first quess do not work. This one does, though:

SELECT 
date,
substr(date,7,4)||'-'||substr(date,4,2)||'-'||substr(date,1,2)||' 00:00:00' as text_repr,
datetime(substr(date,7,4)||'-'||substr(date,4,2)||'-'||substr(date,1,2)||' 00:00:00') as datetime_repr
FROM
t

Simply put - You have to parse it on Your own, as stated here or here...

Community
  • 1
  • 1
T.Z.
  • 2,092
  • 1
  • 24
  • 39
  • Thanks. I tried it using `SELECT date, strftime('%Y-%m-%d %H:%M:%S', date) FROM t;` and it shows the old date `29/06/2007` in column ` and _NULL_ in column 2. –  Mar 03 '15 at 14:47