Android Question Extract only year from date field

[microbox]

Hi, I have the following sql statement.

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]

Hi, I have the following sql statement.

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.

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

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.

DBUtils Example

Latest version available here: https://www.b4x.com/android/forum/threads/b4x-dbutils-2.81280/ This example demonstrates how DBUtils module can be used in B4J. DBUtils was modified to match the slightly different API of B4J jSQL library. Other changes: - ExecuteSpinner and ExecuteListView were...

www.b4x.com

so I copied the code and modified it to work in the best way in b4j

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

https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc

 

[microbox]

@Lucas Siqueira thank you for sharing, I will try it out.