I can't find a solution for my problem.

I have an HTML5 Web SQL Database with a table like this:

db.transaction(function(tx) {   
        tx.executeSql("CREATE TABLE IF NOT EXISTS todo " +
        "(todoId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
        "note VARCHAR(100) NOT NULL, " + 
        "state VARCHAR(100) NOT NULL, " + 
        "todoDate DATETIME NOT NULL)");
});

When I add values to this database (notation = dd-MM-yyyy), it looks like the todoDate is added as a string to the database. When I collect and sort some todoDate values from the database with the following query, the values are sorted in the wrong order:

sql = "select * FROM todo order by todoDate asc";

Output:

             todoId -     note        - state         - todoDate  
             3      -     blabla      - someinfo     - 01-01-2013
             1      -     blabla      - someinfo     - 22-09-2012
             2      -     blabla      - someinfo     - 25-10-2012

I would like to get the following order:

             todoId -     note        - state         - todoDate  
             1      -     blabla      - someinfo     - 22-09-2012
             2      -     blabla      - someinfo     - 25-10-2012
             3      -     blabla      - someinfo     - 01-01-2013

How can I achieve this?

I found the function str_to_date but it doesn't work or I did something wrong.

Thanks in advance!

I can't find a solution for my problem.

I have an HTML5 Web SQL Database with a table like this:

db.transaction(function(tx) {   
        tx.executeSql("CREATE TABLE IF NOT EXISTS todo " +
        "(todoId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
        "note VARCHAR(100) NOT NULL, " + 
        "state VARCHAR(100) NOT NULL, " + 
        "todoDate DATETIME NOT NULL)");
});

When I add values to this database (notation = dd-MM-yyyy), it looks like the todoDate is added as a string to the database. When I collect and sort some todoDate values from the database with the following query, the values are sorted in the wrong order:

sql = "select * FROM todo order by todoDate asc";

Output:

             todoId -     note        - state         - todoDate  
             3      -     blabla      - someinfo     - 01-01-2013
             1      -     blabla      - someinfo     - 22-09-2012
             2      -     blabla      - someinfo     - 25-10-2012

I would like to get the following order:

             todoId -     note        - state         - todoDate  
             1      -     blabla      - someinfo     - 22-09-2012
             2      -     blabla      - someinfo     - 25-10-2012
             3      -     blabla      - someinfo     - 01-01-2013

How can I achieve this?

I found the function str_to_date but it doesn't work or I did something wrong.

Thanks in advance!

• javascript
• html

3 Answers 3

The HTML5 Web SQL Database is actually SQLite under the hood. SQLite doesn't have a DATETIME type. If you send them Strings, it'll store them as Strings. SQLite remends that you use ISO-8601 canonical format (e.g. "2012-09-22") so parisons work as expected. SQLite provides a bunch of useful date time functions for working with date-time values in the database. See here. Alternatively, you can store milliseconds but I personally prefer to store the strings because they are human readable which helps with debugging.

Like that query you can fire :
select * from (select column1,...,convert(date,column) from table) as a order by dateColumn

I found the solution.

Here is the code:

SELECT * FROM tablename ORDER by substr(dateColumn,7)||substr(dateColumn,4,2)||substr(dateColumn,1,2);