SQLite function 'strftime' support

[mindbox]

Is 'strftime' function supported? I'm trying to get from the database the SUM of values for a given month (january 2012).

Dim SQL1 As SQL
SQL1.Initialize(File.DirDefaultExternal, "Database.db", True)
Dim sResult As Float
Dim sSQLQuery As String
sSQLQuery = "SELECT SUM(Value) FROM Invoices WHERE ClientID=1 AND strftime('%m', InvoiceDateTime)='01' AND strftime('%Y', InvoiceDateTime)='2012'"
sResult = SQL1.ExecQuerySingleResult(sSQLQuery)
If sResult = Null Then
   Return 0
Else
   Return sResult
End If

I get the error: "NullPointerException" at this line:

sResult = Main.SQL1.ExecQuerySingleResult(sSQLQuery)

 

[mindbox]

I've solved that using the IFNULL function.

sSQLQuery = "SELECT IFNULL(SUM(Value), 0) FROM Invoices WHERE ClientID=1 AND strftime('%m', InvoiceDateTime)='01' AND strftime('%Y', InvoiceDateTime)='2012'"

But I still get zero, although, there are some values in that month.

 

[mindbox]

I got it working. The 'strftime' function works only if you save the date using the 'YYYY-MM-dd HH:mm' format.

To parse the date in the required format I use this function:

Sub FormatSQLDate(sDate As String, sDateFormat As String) As String
   DateTime.DateFormat = sDateFormat
   Dim dtDate As Long : dtDate = DateTime.DateParse(sDate)
   Dim sMonth As String : sMonth = DateTime.GetMonth(dtDate)
   If sMonth.Length = 1 Then
      sMonth = "0" & sMonth
   End If
   Return DateTime.GetYear(dtDate) & "-" & sMonth & "-" & DateTime.GetDayOfMonth(dtDate)
End Sub