Issue
I have and app and storing some data on SQLite. And i have a column named date and storing string data format like:
30.12.2018 - some data
01.01.2019 - some data
31.12.2018 - some data
02.01.2019 - some data
but when i want to SELECT * FROM table ORDER BY date
, this doesn't work correctly. Can I order this table according to correct date order without changing date format?
E.g. when I use SELECT * FROM mytable ORDER BY date
the desired result:
02.01.2019 - some data
01.01.2019 - some data
31.12.2018 - some data
30.12.2018 - some data
Solution
You must change the format of this column in order to avoid situations
where you need statements like this:
select * from mytable
order by
substr(date, 7, 4) || substr(date, 4, 2) || substr(date, 1, 2) || substr(date, instr(date, '-')) desc
This part:
substr(date, 7, 4) || substr(date, 4, 2) || substr(date, 1, 2)
rearranges the date to YYYYMMDD
so it's comparable and can be sorted.
Answered By - forpas
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.