CODEWITHSUNDEEP
sqlite
Jincy Varghese
Jincy Varghese

Reputation: 41

In SQLite Order By Datetime desc returning wrong data

I have one doubt in Sqlite.dteTime DataType is Varchar(200) in Table structure. I want to get result in order by Date Time.

"select dteTime from table ORDER BY dteTime Desc",

getting result is

"05/05/2015 12:38:43 PM"

"05/05/2015 12:38:43 AM"

"05/05/2015 10:57:04 AM"

"05/05/2015 10:57:04 AM"

"05/05/2015 10:51:25 AM"

"05/05/2015 10:51:25 AM"

"05/05/2015 04:38:35 PM"

"05/05/2015 04:00:48 PM"

"05/04/2015 11:38:43 PM"

Instead of this how can i get(Expected Result)

"05/05/2015 04:38:35 PM"

"05/05/2015 04:00:48 PM"

"05/05/2015 12:38:43 PM"

"05/05/2015 10:57:04 AM"

"05/05/2015 10:57:04 AM"

"05/05/2015 10:51:25 AM"

"05/05/2015 10:51:25 AM"

"05/05/2015 12:38:43 AM"

"05/04/2015 11:38:43 PM"

Any solution?

Upvotes: 4

Views: 1097

Answers (2)

MPelletier
MPelletier

Reputation: 16697

SQLite doesn't have an internal date type. It's sorting alphabetically in a case like this. You can write your dates in an iso format or transform them to do the sort.

Upvotes: 2

laalto
laalto

Reputation: 152867

Use a date format such as unixtime (seconds/milliseconds since an epoch) or ISO 8601 (e.g. yyyy-MM-dd'T'HH:mm:ssZ) for your datetime stamps so that the natural sort order is also the chronological order.

Upvotes: 2

Related Questions