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
@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)
Heres the code
sqlquery = "Select * from tbltrans where Date >='" & FromDateEdit.text & "' And Date <='" & statetoDate.text & "'"
cursor1 = SQL1.ExecQuery(sqlquery)
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.
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
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"
@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.
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
@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.
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.