Faster date parser?

Discussion in 'Questions (Windows Mobile)' started by RB Smissaert, Jan 1, 2009.

  1. RB Smissaert

    RB Smissaert Well-Known Member Licensed User

    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:

    Code:
    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, 64) & _
                                                        SubString(strDate, 
    32) & _
                                                        SubString(strDate, 
    02)
          
    '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
     
  2. Erel

    Erel Administrator Staff Member Licensed User

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

    RB Smissaert Well-Known Member Licensed User

    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
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice