D daniedb Active Member Licensed User Longtime User Oct 8, 2014 #1 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
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 Oct 8, 2014 #2 Code: Copy dim sqlquery as string sqlquery = "Select * from mytable where datefield >='" & searchfromdate.text & "' And datefield <='" & searchtodate.text & "'" Upvote 0
Code: Copy dim sqlquery as string sqlquery = "Select * from mytable where datefield >='" & searchfromdate.text & "' And datefield <='" & searchtodate.text & "'"
D daniedb Active Member Licensed User Longtime User Oct 8, 2014 #3 Thanks a Mil, Reviewnow Works 100% Cherio Danie Upvote 0
M Mahares Expert Licensed User Longtime User Oct 9, 2014 #4 @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
@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)
D daniedb Active Member Licensed User Longtime User Oct 9, 2014 #5 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
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
Erel B4X founder Staff member Licensed User Longtime User Oct 9, 2014 #6 Don't build your queries like this. Instead: Code: Copy Cursor1 = SQL.ExecQuery2("Select * From myTable where DATE_P >= ? AND DATE_P <= ?", Array As String(DS, DE)) Upvote 0
Don't build your queries like this. Instead: Code: Copy Cursor1 = SQL.ExecQuery2("Select * From myTable where DATE_P >= ? AND DATE_P <= ?", Array As String(DS, DE))
M Mahares Expert Licensed User Longtime User Oct 9, 2014 #7 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
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.
D daniedb Active Member Licensed User Longtime User Oct 9, 2014 #8 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: Oct 9, 2014 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
D daniedb Active Member Licensed User Longtime User Oct 9, 2014 #9 Erel said: Don't build your queries like this. Instead: Code: Copy Cursor1 = SQL.ExecQuery2("Select * From myTable where DATE_P >= ? AND DATE_P <= ?", Array As String(DS, DE)) Click to expand... Thanks Erel, will change code accordingly Cheers Danie Upvote 0
Erel said: Don't build your queries like this. Instead: Code: Copy Cursor1 = SQL.ExecQuery2("Select * From myTable where DATE_P >= ? AND DATE_P <= ?", Array As String(DS, DE)) Click to expand... Thanks Erel, will change code accordingly Cheers Danie
C charlesg Member Licensed User Longtime User Oct 10, 2014 #10 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
Hi Just as a matter of interest: why is execquery2 preferable to execquery? Danie's method is used under execquery in the help file.
K keirS Well-Known Member Licensed User Longtime User Oct 10, 2014 #11 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
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"
M Mahares Expert Licensed User Longtime User Oct 10, 2014 #12 @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
@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.
S sorex Expert Licensed User Longtime User Oct 10, 2014 #13 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
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
K keirS Well-Known Member Licensed User Longtime User Oct 10, 2014 #14 Mahares said: @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. Click to expand... 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: Oct 10, 2014 Upvote 0
Mahares said: @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. Click to expand... 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.
S sorex Expert Licensed User Longtime User Oct 10, 2014 #15 maybe sqlite auto casts to yyyy-mm-dd dates ? (which is the way to go anyway) Upvote 0
K keirS Well-Known Member Licensed User Longtime User Oct 10, 2014 #16 My test was done using SQLite. Upvote 0
S sorex Expert Licensed User Longtime User Oct 10, 2014 #17 using a date or char type or doesn't it matter? Upvote 0
K keirS Well-Known Member Licensed User Longtime User Oct 10, 2014 #18 There is no such thing as a DATE type in SQLite. It only has NULL , REAL, TEXT, INTEGER and BLOB. Upvote 0
S sorex Expert Licensed User Longtime User Oct 10, 2014 #19 ok, that sqlite tool for firefox shows datetime and other stuff aswell tho. Upvote 0
M Mahares Expert Licensed User Longtime User Oct 10, 2014 #20 Sorex says: maybe sqlite auto casts to yyyy-mm-dd dates ? (which is the way to go anyway) Click to expand... 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
Sorex says: maybe sqlite auto casts to yyyy-mm-dd dates ? (which is the way to go anyway) Click to expand... 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.