S stratus Active Member Licensed User Longtime User Jun 6, 2012 #1 I have a database with date fields.My question is how can i get SQLite to sort by date?
specci48 Well-Known Member Licensed User Longtime User Jun 6, 2012 #2 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 Upvote 0
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 Expert Licensed User Longtime User Jun 6, 2012 #3 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. Upvote 0
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.
S stratus Active Member Licensed User Longtime User Jun 6, 2012 #4 specci48 said: 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 Click to expand... Is stored like 5/3/2012 Upvote 0
specci48 said: 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 Click to expand... Is stored like 5/3/2012
S stratus Active Member Licensed User Longtime User Jun 6, 2012 #5 eps said: 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. Click to expand... I would appreciate if you post the code. Upvote 0
eps said: 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. Click to expand... I would appreciate if you post the code.
eps Expert Licensed User Longtime User Jun 6, 2012 #6 stratus said: I would appreciate if you post the code. Click to expand... 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... Upvote 0
stratus said: I would appreciate if you post the code. Click to expand... 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 Well-Known Member Licensed User Longtime User Jun 6, 2012 #7 stratus said: Is stored like 5/3/2012 Click to expand... 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 Code: Copy select * from blah order by ticksColumn For a reverse sort order add a final DESC to the select statement. specci48 Upvote 0
stratus said: Is stored like 5/3/2012 Click to expand... 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 Code: Copy select * from blah order by ticksColumn For a reverse sort order add a final DESC to the select statement. specci48
P poseidon Member Licensed User Longtime User Jun 6, 2012 #8 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 Upvote 0
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 Expert Licensed User Longtime User Jun 6, 2012 #9 Code: Copy 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. Upvote 0
Code: Copy 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.
