Wrong ordering data grid column

[AlessioForconi]

Hi to all

I have a data grid that has three columns connected to a sqlite database.
The first column contains dates and this must be done sorting.
The dates are stored as test on the database since sqlite does not support formed the date / time.
The problem is that sorting does not work, in fact, having
01/01/2015
02/01/2015
03/01/2015
02/03/2015

that would be the correct ordering
instead I get
01/01/2015
02/01/2015
02/03/2015
03/01/2015

The settings of the data grid are correct, I checked the 'sort by column' in the Ascending and interested as I cleared the other.

How can I fix?

Thank you

[Klaus]

Hi Alessio,

you are using -> sort xyz DATETIME, right?
In that case this is correct, since LC always presumes you mean ENGLISH dates!

Even if you "set the usesystemdate to true", the order will be the same,
because you are also using an english date FORMAT!

Can't you store the date in the correct format in your db?
That would make things easier


Best

Klaus

[bhall2001]

The date format is not one of the formats Livecode handles natively for sorting in a data grid. Not to worry though. There's a couple of ways you can handle this. And I just want to be clear that what I think you have for a date format is DD/MM/YYYY.

1) if possible, convert the dates when they are loaded from the database to a format that Livecode handles natively (MM/DD/YYYY) and all will work with no additional coding. When you save the date back to the db you'll have to re-convert the dates back to the format stored in the db.

2) Override the datagrid sort and create a custom sort for the column using the date format you desire. Here's a link to a tutorial on how to do this in general.
http://lessons.runrev.com/m/datagrid/l/ ... mn-sorting

The meat of the code (off the top of my head) is going to be something like the following...

Code: Select all

-- note this is from a button that i put together to check logic
-- THIS IS NOT THE CODE TO USE FOR THE ACTUAL DG SORTING
-- If you create a data grid, put the data in that you have (in DD/MM/YYYY format),
-- create a button and paste in this code to test it.
on mouseUp
   -- Get the text that needs to be sorted
   put the dgText of group "gridSortMe" into tSortMe
   
   -- Convert each line in the datagrid to a standard Livecode date format
   set the itemdelimiter to slash
   repeat for each line i in tSortMe
      put item 2 of i & "/" & item 1 of i & "/" & item 3 of i & return after tTemp
   end repeat
   
   -- Now time to sort the dates,
   -- with "real" code you need to check for direction of sort
   sort tTemp
   put "" into tSortMe
   
   -- Put the date back into the formated desired.
   repeat for each line i in tTemp
      put item 2 of i & "/" & item 1 of i & "/" & item 3 of i & return after tSortMe
   end repeat
   
   -- for now I'm just going to put it up on the screen
   answer tSortMe
end mouseUp

[dunbarx]

bhall2001.

I wish I could annotate like you do.

Craig Newman

[AlessioForconi]

I solved with convert function

Code: Select all

convert insertedDate to system date

Then I set the date column grdi system on dates and everything works as it should.

Thanks for the help