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

prince_j325 · Apr 19, 2020

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

Erel · Apr 19, 2020

What is the output of:

B4X:

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

prince_j325 · Apr 19, 2020

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)

makis_best · Apr 19, 2020

Try to use

B4X:

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

replace '4' with '04'

prince_j325 · Apr 19, 2020

makis_best said:
Try to use

B4X:

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

replace '4' with '04'

Same no result

makis_best · Apr 19, 2020

How the field CaseDate is declared?

prince_j325 · Apr 19, 2020

makis_best said:
How the field CaseDate is declared?

It is saved as Date datatype

udg · Apr 19, 2020

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

prince_j325 · Apr 19, 2020

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

makis_best · Apr 19, 2020

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:

YYYY-MM-DD

YYYY-MM-DD HH:MM

YYYY-MM-DD HH:MM:SS

YYYY-MM-DD HH:MM:SS.SSS

YYYY-MM-DDTHH:MM

YYYY-MM-DDTHH:MM:SS

YYYY-MM-DDTHH:MM:SS.SSS

HH:MM

HH:MM:SS

HH:MM:SS.SSS

now

DDDDDDDDDD

If it is not... Try to use something like

B4X:

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

Erel · Apr 19, 2020

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 · Apr 19, 2020

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 · Apr 19, 2020

prince_j325 said:
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))

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

prince_j325

Member

Erel

B4X founder

prince_j325

Member

makis_best

Well-Known Member

prince_j325

Member

makis_best

Well-Known Member

prince_j325

Member

udg

Expert

prince_j325

Member

makis_best

Well-Known Member

Erel

B4X founder

prince_j325

Member

Mahares

Expert

Similar Threads

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

Member

B4X founder

Member

Well-Known Member

Member

Well-Known Member

Member

Expert

Member

Well-Known Member

B4X founder

Member

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency