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
