Mark.S Member Licensed User Oct 6, 2018 #1 Is there a way of ordering a date column when the saved format is dd/mm/yy
npsonic Active Member Licensed User Oct 6, 2018 #2 You mean something like this? Code: sql1.ExecNonQuery("CREATE TABLE IF NOT EXISTS main(value1 NONE, value2 NONE, date NONE)") sql1.ExecQuery("SELECT * FROM main ORDER BY date DESC") Upvote 0
You mean something like this? Code: sql1.ExecNonQuery("CREATE TABLE IF NOT EXISTS main(value1 NONE, value2 NONE, date NONE)") sql1.ExecQuery("SELECT * FROM main ORDER BY date DESC")
M Mahares Expert Licensed User Longtime User Oct 6, 2018 #3 Mark.S said: Is there a way of ordering a date column when the saved format is dd/mm/yy Click to expand... Here is my take. Use the Substr function: Code: SQL1.ExecQuery("SELECT * FROM main ORDER BY substr(DATE,7,2) || substr(DATE,4,2) || substr(DATE,1,2)" ) Upvote 0
Mark.S said: Is there a way of ordering a date column when the saved format is dd/mm/yy Click to expand... Here is my take. Use the Substr function: Code: SQL1.ExecQuery("SELECT * FROM main ORDER BY substr(DATE,7,2) || substr(DATE,4,2) || substr(DATE,1,2)" )
Mark.S Member Licensed User Oct 6, 2018 #4 Brilliant, thanks Mahares. little by little I learn, didn't realize you could put kind of argument in a select Upvote 0
Brilliant, thanks Mahares. little by little I learn, didn't realize you could put kind of argument in a select
stevel05 Expert Licensed User Longtime User Oct 6, 2018 #5 Sqlite can do complex data manipulation like this but from a design perspective, if you are creating the data I would suggest that you convert the date to a number using the B4x DateTime functions and store that. You can the reconvert it for display and sorting and searching becomes simple and fast. Upvote 0
Sqlite can do complex data manipulation like this but from a design perspective, if you are creating the data I would suggest that you convert the date to a number using the B4x DateTime functions and store that. You can the reconvert it for display and sorting and searching becomes simple and fast.
M Mahares Expert Licensed User Longtime User Oct 6, 2018 #6 My favorite way to store dates without time is this format; yyyymmdd or maybe: yyyy-mm-dd. You can sort asc, desc without any further manipulations. When you see the date, you can easily recognize it as a date. Some in the forum prefer ticks as in B4X Datetime functions. You tell me which one of the following looks like a date when you look at it: the ticks: 1538798400000 or 20181006. For me it is a no-brainer, the second one. But people have different preferences and reasons. Last edited: Oct 6, 2018 Upvote 0
My favorite way to store dates without time is this format; yyyymmdd or maybe: yyyy-mm-dd. You can sort asc, desc without any further manipulations. When you see the date, you can easily recognize it as a date. Some in the forum prefer ticks as in B4X Datetime functions. You tell me which one of the following looks like a date when you look at it: the ticks: 1538798400000 or 20181006. For me it is a no-brainer, the second one. But people have different preferences and reasons.
D Deleted member 103 Guest Oct 6, 2018 #7 stevel05 said: Sqlite can do complex data manipulation like this but from a design perspective, if you are creating the data I would suggest that you convert the date to a number using the B4x DateTime functions and store that. You can the reconvert it for display and sorting and searching becomes simple and fast. Click to expand... this is also my preferit, so you can also sort by hours, minutes and seconds if necessary. Upvote 0
stevel05 said: Sqlite can do complex data manipulation like this but from a design perspective, if you are creating the data I would suggest that you convert the date to a number using the B4x DateTime functions and store that. You can the reconvert it for display and sorting and searching becomes simple and fast. Click to expand... this is also my preferit, so you can also sort by hours, minutes and seconds if necessary.
