ListBox dates

Databases
1

I have a page with two list boxes filled from SQLite database on a Mac. The second displays data based on selection in first Listbox. Works great except one column is date, displays as US date MM/DD/YYYY, and sorts alphabetically. I have read several suggestions on storing date in a celltag and sort using totalSeconds. Is there an example of this I could download to study? Or, in code, can I:

  1. Get date from recordset.
  2. Rearrange date to date.SQLDate.
  3. Sort date?
  4. Put date back in recordset.
  5. Fill the Listbox from the recordset using MM/DD/YYYY format.
    If this can be done, could the date be edited in the Listbox?
    Obviously, I’m new at this and can use some guidance. Thanks to all.
2

store and sort using SQLDate Format

As a matter of fact you should be storing it that way in your database, then you can SELECT using ORDER BY and it will be in the right order to start with

rs.SqlSelect("SELECT * from yourTable ORDER by datefield")
while not rs.eof
    ....get fields
    dt.sqldate=rs.field("datefield").stringvalue
    listbox.addrow dt.shortdate  // <= list box will have m/d/y or whatever shortdate format is per the user
   rs.movenext
wend

This code is for illustration only, and assumes datefield is STORED in the database in SQLDate Format YYYY-MM-DD

3

Thanks. I need to go back into SQLite and see how dates are stored. They came from MS Access as CSV file import with date as data type column. That was in Windows, now on Mac. Think that may have changed date format. Not sure if date is as displayed or stored in database. Thanks again.