Hi Olaf, Thanks, will give that a try today and tell you if it is faster.
RBS On Wed, Oct 3, 2012 at 3:55 AM, Olaf Schmidt <s...@online.de> wrote: > Am 02.10.2012 21:23, schrieb Bart Smissaert: > >> Nice one, thanks for that. > > > Just in case you use the COM-Wrapper - and this operation takes > place in a plain Recordset-Select (and isn't stored somewhere > in the DB itself, e.g. in a Trigger), then you can reduce the > amount of function-calls a bit, when you use something like that: > > Define the Table-Field with the wrapper-supported Time-FieldType, > which ends up as 'hh:mm:ss' Text in the SQLite-DB-Field - but is > correctly translated back into a Date-Type in the receiving cRecordset. > > To safe a few CPU-Cycles in the Query, you can directly place > the Double-representation of a VB(A)-Date in the Query-String > (done in the Example per ? Placeholder in a cSelectCommand). > > The Diff-expression in the Select then looks this way: > CTime(? - CDbl(HMS)) > > CDbl, to convert the TextContent of the HMS-Field into the > Double-representation of a VB-Date - and CTime to > convert the difference back into a 'hh:mm:ss' String. > > Not sure, if that is faster than SQLites built-in Date/Time-Functions, > but worth a try... > > > ' Into a Form (clicking the Form gives the Delta to its "LoadTime") > ' Output then i.e.: HMS 04:21:27 True DTS 00:00:01 True > Option Explicit > > Private Cnn As New cConnection, GetDeltaCmd As cSelectCommand > > Private Sub Form_Load() > Cnn.CreateNewDB '<- InMemory > > 'the wrappers Time-FieldType ensures 'hh:mm:ss' TextFormat in the DB > Cnn.Execute "Create Table T(HMS Time)" > > With Cnn.CreateCommand("Insert Into T Values(?)") > .SetTime 1, Now() > .Execute > End With > > Const GetDeltaSQL = "Select HMS, CTime(? - CDbl(HMS)) As DTS From T" > Set GetDeltaCmd = Cnn.CreateSelectCommand(GetDeltaSQL) > End Sub > > Private Sub Form_Click() > GetDeltaCmd.SetDouble 1, Now() 'we place the Param directly as Double > > With GetDeltaCmd.Execute 'returns a cRecordset > Debug.Print !HMS.Name, !HMS.Value, VarType(!HMS.Value) = vbDate, > Debug.Print !DTS.Name, !DTS.Value, VarType(!DTS.Value) = vbString > End With > End Sub > > Olaf > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users