CODEWITHSUNDEEP
datesqlite
user28490
user28490

Reputation: 1037

SQLITE order by date does not work

I have the following DDL in SQLite:

CREATE TABLE money (
    id INTEGER PRIMARY KEY,
    'when' TIMESTAMP,
    amount INTEGER,
    note TEXT
) ;

I tried to view rows ordered by date:

sqlite3 -line money.db "select * from money order by datetime('when') desc limit 5"

But it does not return rows ordered by 'when' in reversed order. Also changing desc to asc gives exactly the same results. Tried also order by 'when' instead of order by datetime('when'). What should be the right query? Thanks!

Upvotes: 2

Views: 9611

Answers (1)

CL.
CL.

Reputation: 180172

In SQL, strings use single quotes, while table/column names can be quoted with double quotes.

SQLite accepts both kinds of quotes for compatibility with MySQL, but this works only when the intended meaning is umambiguous. In the function call datetime('when'), when is interpreted as a string.

Use datetime("when").

Upvotes: 4

Related Questions