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

[prince_j325]

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 **

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"))

 

[Erel]

What is the output of:

SELECT  strftime('%m', CaseDate) FROM tblCases

 

[prince_j325]

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

** 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)

 

[makis_best]

Try to use

sql="SELECT * FROM tblCases WHERE strftime('%m', CaseDate)='04' "

replace '4' with '04'

 

[prince_j325]

Try to use

sql="SELECT * FROM tblCases WHERE strftime('%m', CaseDate)='04' "

replace '4' with '04'

Same no result

 

[makis_best]

How the field CaseDate is declared?

 

[prince_j325]

How the field CaseDate is declared?

It is saved as Date datatype

 

[udg]

Am I wrong or CaseDate should be in the form yyyy-mm-dd in the DB?

 

[prince_j325]

The version of SQL in Library im using is (Version 1.50).
Maybe it is not supported?

 

[makis_best]

In my database I use the script

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

sql="SELECT * FROM tblCases WHERE strftime('%m', strftime('%Y-%m-%d',CaseDate))='04' "

 

[Erel]

1. It is a mistake to use Cursor instead of ResultSet.
2. It is the OS SQLite engine that handles this command.
3. My guess is that the error you got in post #3 is related to Null values in the database.

 

[prince_j325]

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.

 

[Mahares]

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:

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))