Android Question Extract only year from date field

microbox

Active Member
Licensed User
Longtime User
Hi, I have the following sql statement.
B4X:
DBUtils.ExecuteSpinner(SQL, "SELECT strftime('%Y', datein) As Year FROM payables order  by cid desc", Null, 0, spnryr)
but I'm getting null result.

regards to all.
 

Lucas Siqueira

Active Member
Licensed User
Hi, I have the following sql statement.
B4X:
DBUtils.ExecuteSpinner(SQL, "SELECT strftime('%Y', datein) As Year FROM payables order  by cid desc", Null, 0, spnryr)
but I'm getting null result.

regards to all.


your query is correct, but you should note two things.

if the datein field of your table is as a date and if your date is yyyy-mm-dd.

1619266304464.png
1619266325850.png


when I run the query I get the return exactly as you request.
1619266374243.png


another point that should be noted is some error when defining the values in your spinner, I did a test using b4j. the code is attached.

ExecuteSpinner has been disabled in the latest version of dbutils.

so I copied the code and modified it to work in the best way in b4j
B4X:
Private Sub Button1_Click
    Dim SQL As SQL
    Dim name_db As String = "teste.db"
   
    Wait For (File.CopyAsync(File.DirAssets, name_db, File.DirApp, name_db)) Complete (Success As Boolean)
    Log("Success: " & Success)
   
    SQL.InitializeSQLite(File.DirApp, name_db, False)
   
    'https://www.b4x.com/android/forum/threads/dbutils-example.34611/#content
    '- ExecuteSpinner and ExecuteListView were removed.
    'DBUtils.ExecuteSpinner(SQL, "SELECT strftime('%Y', datein) As Year FROM payables order  by cid desc", Null, 0, spnryr)
   
    ExecuteSpinner(SQL, "SELECT strftime('%Y', datein) As Year FROM payables order  by cid desc", Null, 0, spnryr)
End Sub

Sub ExecuteSpinner(xsql As SQL, xquery As String, xstring_args() As String, xlimit As Int, xspinner As Spinner)
   
    Dim itens As List
    itens.Initialize
    itens.Clear
   
    Dim Table As List
    Table = DBUtils.ExecuteMemoryTable(xsql, xquery, xstring_args, xlimit)
    Dim Cols() As String
    For i = 0 To Table.Size - 1
        Cols = Table.Get(i)
       
        #if b4a
            xspinner.Add(Cols(0))
        #End If
       
        #if b4j
            itens.Add(Cols(0))
        #End If
    Next
   
    #if b4j
    xspinner.SetListItems(itens)
    #End If
End Sub


I used the most recent sqlite library: sqlite-jdbc-3.34.0.jar
 

Attachments

  • TESTE3.zip
    6.2 KB · Views: 42
Upvote 0
Top