Faster date parser?

[RB Smissaert]

Best wishes for 2009 to all users of this forum.

Sorting a table on a date column in the format dd/mm/yyyy
To sort this properly I add an invisible column with date parsed yyyymmdd:

For i = 0 To Table1.RowCount -1
   strDate = Table1.Cell(strDateColumn, i)
   If StrLength(strDate) > 0 Then
      'this is a bit faster than DateParse
      '-----------------------------------
'         Table1.Cell(strNewColumn, i) = SubString(strDate, 6, 4) * 10000 + _
'                                                       SubString(strDate, 3, 2) * 100 + _
'                                                       SubString(strDate, 0, 2)
      Table1.Cell(strNewColumn, i) = SubString(strDate, 6, 4) & _
                                                    SubString(strDate, 3, 2) & _
                                                    SubString(strDate, 0, 2)
      'Table1.Cell(strNewColumn, i) =   DateParse(strDate)
   Else
      Table1.Cell(strNewColumn, i) = 0
   End If
Next i

Now on the device this is quite slow, some 8 secs for 400 rows.
One way to solve this would be to add the extra column in the desktop
application (a VB6 dll), but I wonder if there is any faster way to do this
on the device.


RBS

 

[Erel]

You should add a hidden column with the ticks value and then sort this column. It should be very fast.

 

[RB Smissaert]

You should add a hidden column with the ticks value and then sort this column. It should be very fast.

I found that I added a new column to the table and parsed the dates and moved them to the new column (in a loop) it was quite slow.
I solved this now by letting SQLite do the work and make the table with the yyyymmdd column through the SQLite select, so:

SELECT
Applies, Medication, Read_Code, Entry_Id,
substr(Applies, 7, 4) || substr(Applies, 4, 2) || substr(Applies, 1, 2)
AS Applies_Values
FROM
AUTHORISATIONS_PAT_PPP

Where the field Applies is in the dd/mm/yyyy format in a SQLite table.
Adding this extra column gives very little overhead.


RBS