SQLite query to update some (not all) date formats

Question

My database currently has a column which looks like this:

id | timestamp
-- | ---------
1  | 07/29/2014
2  | 07/30/2014
3  | 07/30/2014
4  | 2014-07-30
5  | 2014-07-30

I am trying to format any rows of timestamp in the old format, to the new format

in this example, rows 1-3 need to be reformatted

07/29/2014 -> 2014-07-29
07/30/2014 -> 2014-07-30
07/30/2014 -> 2014-07-30

I am not that familiar with SQL/SQLite but and from to come up with some sql i can execute once to see if there are any rows in the old format, and if they are, replace them with the equivalent of the new format.

any help would be greatly appreciated.

I believe I am trying to do what this user here wrote but couldn't get it to work

https://stackoverflow.com/a/7781473/433866

Thanks!

Here are the results I am getting with some real data:

timestamp column old / timestamp converted
07/25/2014 -> 01/04/0031
05/21/2014 -> 11/04/0026
01/25/2013 -> 07/06/0030
09/25/2000 -> 02/21/0031
07/25/2015 -> 01/05/0031
07/25/2016 -> 01/06/0031
07/25/1999 -> 12/20/0030
07/25/1986 -> 12/07/0030

and here is the method i am using

// REFORMAT DATES
    public void reformatDates(String table){
        SQLiteDatabase db = this.getWritableDatabase();
        String selectQuery = "SELECT  * FROM " + table;
        Cursor cursor = db.rawQuery(selectQuery, null);
        cursor.moveToFirst();
        if (!cursor.isAfterLast()) {
            do {
                db.execSQL("update Food set timestamp = substr(timestamp,4,2) || '-' || substr(timestamp, 1,2) || '-' || substr(timestamp, 7) where timestamp LIKE '%/%'");
            }
            while (cursor.moveToNext());
        }
        cursor.close();

        db.close();
    }

Did you solve it or not? You changed the question and deleted your own answer...? — Joachim Isaksson, Jul 31 '14 at 21:06
I thought I had but I threw more test data in and it didn't work. It also screwed up my good data. — Rob, Jul 31 '14 at 21:12
And what I changed in my question was updating the original bad dates adding a 0 before the 7. Old format was MM/dd/yyyy — Rob, Jul 31 '14 at 21:13
You just need to work on your WHERE clause, then. Do something simple to restrict it to only your old date format, say `WHERE timestamp LIKE '%/%'`—that will only pick rows with a "/" character in the timestamp ("%" is a wildcard in SQL LIKE comparisons.) — Matt Gibson, Jul 31 '14 at 21:15
I know it's not such helpful comment on this specific problem, but something to think on future projects. Do never store date as localised specific data. Date on SQL (specially on Android) should be store as an EPOCH `integer` that you acquire using `System.currentTimeInMilis()` and can be easily re-constructed to a `Date` or `Calendar` object and then sub-subsequently formatted using a `DateFormat` object — Budius, Jul 31 '14 at 22:11
I updated the results I am getting, for some reason I am getting some really weird numbers — Rob, Jul 31 '14 at 23:54
Your code [appears to be fine](http://sqlfiddle.com/#!7/912ea/4/0). Though you don't need to execute it over and over again in a cursor like that. Just running it once should fix all the rows. But running it multiple times shouldn't break everything like that. And I can't see how your query—with hardcoded hyphens in it—could possibly result in anything with slashes in it. Can we have a sanity check? I'd like to see your code that created the table in the first place, just to check the data types, etc. — Matt Gibson, Aug 01 '14 at 00:25
Got it for real this time! Answer posted. Thanks everyone for the help! Thank you Matt! — Rob, Aug 01 '14 at 00:26

Rob · Answer 1 · 2014-08-01T00:26:17.733

0

Got it this time!

// REFORMAT DATES
    public void reformatDates(String table){
        SQLiteDatabase db = this.getWritableDatabase();
        String selectQuery = "SELECT  * FROM " + table;
        Cursor cursor = db.rawQuery(selectQuery, null);
        cursor.moveToFirst();
        if (!cursor.isAfterLast()) {
            do {
                db.execSQL("update " + table + " set timestamp = substr(timestamp, 7) || '-' || substr(timestamp, 1,2) || '-' || substr(timestamp,4,2) WHERE timestamp LIKE '%/%'");
            }
            while (cursor.moveToNext());
        }
        cursor.close();

        db.close();
    }

edited Aug 01 '14 at 00:26

answered Jul 31 '14 at 20:48

Rob

1,162
2
18
43

You should be able to replace everything between `String selectQuery...` and `cursor.close();` inclusive with just your db.execSQL("update...") statement. There's no need for the select or to cursor through the results. Just run that one statement, once. It will update all the matching rows for you in one go. – Matt Gibson Aug 01 '14 at 00:30
Thank you Matt, I will make the change. – Rob Aug 01 '14 at 00:55

SQLite query to update some (not all) date formats

1 Answers1