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
 
Code: Copy
dim sqlquery as string
sqlquery = "Select * from mytable where datefield >='" & searchfromdate.text & "' And datefield <='" & searchtodate.text & "'"
 
Upvote 0
@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.

Code: Copy
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
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
Actually it is more efficient and more speedy to have the query as follow:
Code: Copy
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
Hi Mahares

Here is how I did it.
Code: Copy
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
Code: Copy
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
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
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

Code: Copy
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 :

Code: Copy
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
@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
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.

Code: Copy
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: 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:

Code: Copy
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.

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

It returns

Code: Copy
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
There is no such thing as a DATE type in SQLite. It only has NULL , REAL, TEXT, INTEGER and BLOB.
 
Upvote 0
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
Back
Top