Android Question strftime('%m', CaseDate)='4' Won't work

prince_j325

Member
Here are the sample result in Log.
4/10/2020
521
81
4/11/2020
555
82
4/12/2020
607
83
4/13/2020
665
84
4/14/2020
714
85
4/15/2020
784
86
4/16/2020
840
87
4/17/2020
906
** Activity (mdlcharts) Resume **

B4X:
Dim sql As String
    sql="SELECT * FROM tblCases WHERE strftime('%m', CaseDate)='4' " But when I use this code it does not return data.
'    sql="SELECT* FROM tblCases WHERE CaseDate ='4/22/2020'"
'    sql="SELECT* FROM tblCases" I used this first to display all data in Log
    cursor1 = SQL1.ExecQuery(sql)
  
    For i = 0 To cursor1.RowCount - 1
        cursor1.Position = i 
      
        Log(cursor1.GetString("caseid"))
        Log(cursor1.GetString("CaseDate"))
        Log(cursor1.GetString("CaseQty"))
 

prince_j325

Member
B4X:
Dim sql As String


    sql="SELECT strftime('%m', CaseDate) as result FROM tblCases "
    cursor1 = SQL1.ExecQuery(sql)
        
    For i = 0 To cursor1.RowCount - 1
        cursor1.Position = i   
        Log(cursor1.GetString("result"))       
    Next

B4X:
** Activity (mdlcharts) Create, isFirst = true **
Error occurred on line: 93 (mdlCharts)
java.lang.NullPointerException: Attempt to invoke virtual method 'int java.lang.String.length()' on a null object reference
    at anywheresoftware.b4a.BA.addLogPrefix(BA.java:587)
    at anywheresoftware.b4a.keywords.Common.LogImpl(Common.java:191)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:777)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:354)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:180)
    at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:262)
    at b4a.example.mdlcharts._gettotaldata(mdlcharts.java:387)
    at b4a.example.mdlcharts._activity_create(mdlcharts.java:376)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:351)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at b4a.example.mdlcharts.afterFirstLayout(mdlcharts.java:104)
    at b4a.example.mdlcharts.access$000(mdlcharts.java:17)
    at b4a.example.mdlcharts$WaitForLayout.run(mdlcharts.java:82)
    at android.os.Handler.handleCallback(Handler.java:907)
    at android.os.Handler.dispatchMessage(Handler.java:105)
    at android.os.Looper.loop(Looper.java:216)
    at android.app.ActivityThread.main(ActivityThread.java:7625)
 
Last edited:
Upvote 0

udg

Expert
Licensed User
Am I wrong or CaseDate should be in the form yyyy-mm-dd in the DB?
 
Upvote 0

makis_best

Well-Known Member
Licensed User
In my database I use the script
B4X:
SELECT * FROM LOCAL_EGM_Kartela WHERE strftime('%m', RegistrationDate) = '04'
And working just fine.
I think something is wrong with your field declaration.

Also need to know that format should be one of
Time Strings
A time string can be in any of the following formats:
  1. YYYY-MM-DD
  2. YYYY-MM-DD HH:MM
  3. YYYY-MM-DD HH:MM:SS
  4. YYYY-MM-DD HH:MM:SS.SSS
  5. YYYY-MM-DDTHH:MM
  6. YYYY-MM-DDTHH:MM:SS
  7. YYYY-MM-DDTHH:MM:SS.SSS
  8. HH:MM
  9. HH:MM:SS
  10. HH:MM:SS.SSS
  11. now
  12. DDDDDDDDDD

If it is not... Try to use something like
B4X:
sql="SELECT * FROM tblCases WHERE strftime('%m', strftime('%Y-%m-%d',CaseDate))='04' "
 
Last edited:
Upvote 0

prince_j325

Member
Thank you for the help guys!
It's now working, I changed the format to yyyy-MM-dd. The previous one is MM-dd-yyyy.
Maybe that's one of the reason.
 
Upvote 0

Mahares

Expert
Licensed User
sql="SELECT * FROM tblCases WHERE strftime('%m', CaseDate)='4' " But when I use this code it does not return data.
Using Substr in this case is a lot simpler than strftime:
B4X:
Dim sql As String = "SELECT * FROM tblCases WHERE Substr(CaseDate, 1,1)= ?"   'since your date starts with the month
cursor1 = sql.ExecQuery2(sql, Array As Int(4))
 
Upvote 0
Top