Sort database

[stratus]

I have a database with date fields.My question is how can i get SQLite to sort by date?

 

[specci48]

In which way is the date stored within the database?
In a display-format (e.g. 05/03/2012), as a timestamp or in ticks?


specci48

 

[eps]

select * from blah where x=z order by date;

I think it only likes the dates to be in the format CCYY-MM-DD for that to work though.

I can post code, later on, if you still need it.

 

[stratus]

In which way is the date stored within the database?
In a display-format (e.g. 05/03/2012), as a timestamp or in ticks?


specci48

Is stored like 5/3/2012

 

[stratus]

select * from blah where x=z order by date;

I think it only likes the dates to be in the format CCYY-MM-DD for that to work though.

I can post code, later on, if you still need it.

I would appreciate if you post the code.

 

[eps]

I would appreciate if you post the code.

This is the code...

select * from blah where x=z order by date;

to slightly expand...

of course it depends on your code!!!

select * from <table> where selection_criteria = something order by column_name;

This is where column_name is a date field on the table.

Better of looking here : SQLite Query Language: SELECT

I can't give you a lot more than that as I don't know what your database table(s) are called or what the structure of them is either...

 

[specci48]

Is stored like 5/3/2012

Are you the owner of the database? Are you storing and building up the data for yourself? If yes
a) store the date information as ticks and use DateFomat if you want to display a date within the application
or
b) add an additional column to your table to store the ticks value separate to the displayable format

With this you can use

select * from blah order by ticksColumn

For a reverse sort order add a final DESC to the select statement.


specci48

 

[poseidon]

re

you have to store the dates as

yyyy-mm-dd

so will be :
2001-01-01
2000-12-29

then use order by mentioned before

 

[eps]

   Cursor = SQL1.ExecQuery("SELECT _id, name, enddate, display_date FROM event order by startdate,enddate")

That's actual code I've used, but as mentioned it depends on your DB table(s) and table structure...

I would recommend testing out the SQL select part in a SQLLite tool of your choice (mine is the Firefox add-on) and then inserting into an Exec Query.

Of course DBUtils may be used - I don't think it was around when I was looking at doing this.

and of course you've got to place the returned items into a ListView, ScrollView or similar.