Android Question Filter SQlite on Date stored in a text field

daniedb

Active Member
Licensed User
Longtime User
Hi Guys

Searching the Forum, but could find anything. Sorry if a Missed that

I have a date saved in a text field in a SQlite Table (format = dd/mm/yyyy)
Will it be possible to search the DB according to date
eg:
select * from dbtable where .....
filter : sql.date => searchfromdate.text and sql.date <= searchtodate.text

Thanks a Mil
Cheers
Danie
 

Reviewnow

Active Member
Licensed User
Longtime User
B4X:
dim sqlquery as string
sqlquery = "Select * from mytable where datefield >='" & searchfromdate.text & "' And datefield <='" & searchtodate.text & "'"
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
@daniedb:
Are you sure it works? If you format your date field as DATE_P TEXT with this format: dd/MM/yyyy and run the following query, you will get no records even if you have records between January 1 and May 1. Therefore, I do not think it works, unless I am missing something.

B4X:
Dim DS As String= "01/01/2014"
  Dim DE As String= "01/05/2014"
  txt="Select * from MyTable where DATE_P >= '" & DS & "' AND DATE_P <= '" & DE & "'"
  Cursor1=SQL1.ExecQuery(txt)
 
Upvote 0

daniedb

Active Member
Licensed User
Longtime User
Hi Mahares
Yes it works

Heres the code
sqlquery = "Select * from tbltrans where Date >='" & FromDateEdit.text & "' And Date <='" & statetoDate.text & "'"
cursor1 = SQL1.ExecQuery(sqlquery)


Return the correct result everytime

CHeers
Danie
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Actually it is more efficient and more speedy to have the query as follow:
B4X:
Cursor1 = SQL.ExecQuery2("Select * From myTable where DATE_P BETWEEN ? AND  ?", Array As String(DS, DE))
With BETWEEN the expression is only evaluated once.
But that is besides the point. I have created a project with a date field in this format: dd/MM/YYYY and the query does not return any records despite records in the table between the filters. To evaluate the expression properly, you need the date as ticks or YYYYMMDD or YYYY/MM/DD. Can someone else please verify. Danie may have to show more code to convince me how he did it.
 
Upvote 0

daniedb

Active Member
Licensed User
Longtime User
Hi Mahares

Here is how I did it.
B4X:
Sub FilterStatement
Dim totalbalance As Double
stateview.Clear   'need to clear the list
Dim sqlquery As String

   If FromDateEdit.Text = "" OR statetoDate.Text = "" Then
   ToastMessageShow("Search for All Transaction in Database", True)
   cursor1 = SQL1.ExecQuery("SELECT * FROM tbltrans")
   Else
   ToastMessageShow("Search Transactions from " &FromDateEdit.Text&" to "&statetoDate.Text, True)
  sqlquery = "Select * from tbltrans where Date >='" & FromDateEdit.text & "' And Date <='" & statetoDate.text & "'"
   cursor1 = SQL1.ExecQuery(sqlquery)
   End If
For i = 0 To cursor1.RowCount - 1
cursor1.Position = i
   If i = 0 Then
     l_id =cursor1.GetString("ID")
     l_date = cursor1.GetString("Date")
     l_ref = cursor1.GetString("Reference")
     l_descp = cursor1.GetString("Description")
     l_amt = cursor1.GetString("Amount")
     l_ie = cursor1.GetString("IncomeExpense")
     previousamount=0
     If l_ie = "Income" Then
     balance = l_amt
     End If
     If l_ie = "Expense" Then
     balance = balance - l_amt
     End If
   Else
     l_id =cursor1.GetString("ID")
     l_date = cursor1.GetString("Date")
     l_ref = cursor1.GetString("Reference")
     l_descp = cursor1.GetString("Description")
     l_amt = cursor1.GetString("Amount")
     l_ie = cursor1.GetString("IncomeExpense")
     cursor1.Position = i-1
     previousamount=cursor1.GetString("Amount")
     cursor1.Position = i+1
     If l_ie = "Income" Then
     balance = balance + l_amt
     End If
     If l_ie = "Expense" Then
     balance = balance - l_amt
     End If
     
   End If
   
   stateview.AddTwoLines("" &l_date& ", R "& l_amt & " > " & l_ie&  "" , "" & l_descp & " , Balance > R " & NumberFormat(balance,1,2)& "" )
   stateview.TwoLinesLayout.ItemHeight = 80
Next
End Sub

The Fromdate.text and Statetodate.text are filled from a Calender lookup
B4X:
Sub statetobtn_Click
   Dim Dd As DateDialog
   Dd.Year = DateTime.GetYear(DateTime.Now)
   Dd.Month = DateTime.GetMonth(DateTime.Now)
   Dd.DayOfMonth = DateTime.GetDayOfMonth(DateTime.Now)
   ret = Dd.Show("Set the required date", "Select To Date", "Yes", "No", "Cancel", bmp)
   If ret = -1 Then
    statetoDate.Text=Dd.DayOfMonth&"/"&Dd.Month&"/"&Dd.year
   End If
End Sub

Sub statefrombtn_Click
Dim Dd As DateDialog
   Dd.Year = DateTime.GetYear(DateTime.Now)
   Dd.Month = DateTime.GetMonth(DateTime.Now)
   Dd.DayOfMonth = DateTime.GetDayOfMonth(DateTime.Now)
   ret = Dd.Show("Set the required date", "Select From Date", "Yes", "No", "Cancel", bmp)
   If ret = -1 Then
    FromDateEdit.Text=Dd.DayOfMonth&"/"&Dd.Month&"/"&Dd.year
   End If
End Sub



Hope it helps
Cheers
Danie
 
Last edited:
Upvote 0

daniedb

Active Member
Licensed User
Longtime User
Don't build your queries like this.

Instead:
B4X:
Cursor1 = SQL.ExecQuery2("Select * From myTable where DATE_P >= ? AND DATE_P <= ?", Array As String(DS, DE))

Thanks Erel, will change code accordingly
Cheers Danie
 
Upvote 0

charlesg

Member
Licensed User
Longtime User
Hi

Just as a matter of interest: why is execquery2 preferable to execquery? Danie's method is used under execquery in the help file.
 
Upvote 0

keirS

Well-Known Member
Licensed User
Longtime User
Several ways of getting what you want to work. Probably the easiest is for you to reformat your searchfromdate.text and searchtodate.text so they are in "YYYYMMDD" format. Then your code would look something like this

B4X:
csql = 'select * From dbtable  where (substr(dbtable.date,7,4) || substr(dbtable.date,4,2) || substr(dbtable.date,1,2)) >= ' & FromDate _
& " AND (substr(dbtable.date,7,4) || substr(dbtable.date,4,2) || substr(dbtable.date,1,2)) <= " & ToDate

to generate the SQL statement :

B4X:
select * From dbtable where (substr(dbtable.date,7,4) || substr(dbtable.date,4,2) || substr(dbtable.date,1,2)) >= "20110925" AND (substr(dbtable.date,7,4) || substr(dbtable.date,4,2) || substr(dbtable.date,1,2)) <= "20111025"
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
@keirS: You are missing the point. DanieDB claims that his query works even when he leaves the date in this format: dd/MM/yyyy without any further manipulation, which I have disputed. To get the query to work one must manipulate the date using the substr as you suggested or to you need the date formatted as ticks or YYYYMMDD or YYYY/MM/DD.

@daniedb: I would really like to see what you used for a start and date range and what data it returned and if you tested it under different date ranges and looked at the result carefully. I am still not convinced you get the correct result set every time when you store your date as dd/MM/yyyy text field in a SQLite table.

@charlesg: execquery2 and execquery are identical except that with the ExecQuery2 you avoid all the single quotes and double quotes in the SQL statement which can easily introduce errors and become hard to debug. It is particularly more beneficial to use ExecQuery2 in an INSERT statement when you have a table with several fields to fill.
 
Upvote 0

sorex

Expert
Licensed User
Longtime User
actually as long as you keep the format DD/MM/YYYY it will work since it's a plain text sort.

you just need to be sure that it won't become 3/4/2014 but 03/04/2014 or it will fail in this method since it will sort the "/" to/width a numeric value.

B4X:
09/09/2014 good
09/9/2014 bad
9/09/2014 bad
9/9/2014 bad

above you can see it more clear where the sort/compare will fail
 
Upvote 0

keirS

Well-Known Member
Licensed User
Longtime User
@keirS: You are missing the point. DanieDB claims that his query works even when he leaves the date in this format: dd/MM/yyyy without any further manipulation, which I have disputed. To get the query to work one must manipulate the date using the substr as you suggested or to you need the date formatted as ticks or YYYYMMDD or YYYY/MM/DD.

@daniedb: I would really like to see what you used for a start and date range and what data it returned and if you tested it under different date ranges and looked at the result carefully. I am still not convinced you get the correct result set every time when you store your date as dd/MM/yyyy text field in a SQLite table.

He is wrong it won't work. I have plenty of data in British date format (millions of records) as one of my apps is written to import data where dates are in British format.

Given the following dates:

B4X:
09/03/2011
11/09/2011
18/09/2011
25/09/2011
02/10/2011
09/10/2011
16/10/2011
23/10/2011
30/10/2011
31/10/2011
06/11/2011
13/11/2011
20/11/2011
27/11/2011
05/12/2011
06/12/2011
07/12/2011
08/12/2011
22/02/2012
23/03/2012

The following SQL statement should return zero records.

B4X:
select distinct date from date where date > "23/03/2012" order by date

It returns

B4X:
25/09/2011
23/10/2011
30/10/2011
31/10/2011
27/11/2011

Which is exactly what you would expect form a straight string valuation comparison.
 
Last edited:
Upvote 0

sorex

Expert
Licensed User
Longtime User
maybe sqlite auto casts to yyyy-mm-dd dates ? (which is the way to go anyway)
 
Upvote 0

keirS

Well-Known Member
Licensed User
Longtime User
There is no such thing as a DATE type in SQLite. It only has NULL , REAL, TEXT, INTEGER and BLOB.
 
Upvote 0

sorex

Expert
Licensed User
Longtime User
ok, that sqlite tool for firefox shows datetime and other stuff aswell tho.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
Sorex says:
maybe sqlite auto casts to yyyy-mm-dd dates ? (which is the way to go anyway)

No. SQlite does not cast dd/MM/yyyy to yyyy-mm-dd. If you look at Danie's code, he is not simply interested in a simple string comparison. He is interested in a real and authentic date comparison as this is a banking application with expenses and income. Now, that keirS and I dispute his argument that dd/MM/yyyy works, I am interested in seeing what Danie's response to my question in post #12 is.
 
Upvote 0
Top