CODEWITHSUNDEEP
sqlitedate
user1592380
user1592380

Reputation: 36227

Reformat dates in column

I have some data in an SQLite DB of the form:

id  column1  date
111 280 1/1/2014
114 275 1/2/2014

The date field is of type TEXT. I've been made aware (https://www.sqlite.org/lang_datefunc.html) that I should have the dates formatted like YYYY-MM-DD to take advantage of SQLite's datetime functionality. Is there a query I could run to change the format from 

mm/dd/yyyy

to 

YYYY-MM-DD

in place?

Upvotes: 1

Views: 821

Answers (2)

Bill Bell
Bill Bell

Reputation: 21643

Without any frills such as exception handling!

This approach is slightly simpler because strptime doesn't mind about presence or absence of leading zeroes in days and months. 

>>> from datetime import datetime
>>> import sqlite3
>>> con = sqlite3.connect(':memory:')
>>> cur = con.cursor()
>>> cur.execute('CREATE TABLE EXAMPLE (date_column text)')
<sqlite3.Cursor object at 0x00000000038D07A0>
>>> cur.execute('INSERT INTO EXAMPLE VALUES ("1/1/2014")')
<sqlite3.Cursor object at 0x00000000038D07A0>
>>> def transformDate(aDate):
...     tempDate = datetime.strptime(aDate, '%d/%m/%Y')
...     return tempDate.strftime('%Y-%m-%d')
... 
>>> transformDate('1/1/2014')
'2014-01-01'
>>> con.create_function('transformDate', 1, transformDate)
>>> cur.execute('UPDATE EXAMPLE SET date_column = transformDate(date_column)')
<sqlite3.Cursor object at 0x00000000038D07A0>

Upvotes: 1

CL.
CL.

Reputation: 180020

Your current date format has four possible forms:

m/d/yyyy
m/dd/yyyy
mm/d/yyyy
mm/dd/yyyy

To rearrange the fields, extract them with substr() and then combine them again.

It might be possible to determine the positions of the slashes with instr(), but for a one-off conversion, just using four queries is simpler:

UPDATE MyTable
SET date = substr(date, 6, 4) || '-' ||
           substr(date, 1, 2) || '-' || '0' ||
           substr(date, 4, 1)
WHERE date LIKE '__/_/____';

-- this is mm/d/yyyy; similarly for the other forms, modify positions and zeros

Upvotes: 2

Related Questions