Android Question Filter SQlite on Date stored in a text field

[daniedb]

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

 

[sorex]

indeed...

1.2 Date and Time Datatype
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

strange that his DD/MM/YYYY gets treated right then.

 

[Dman]

In my app I stored dates as MM/dd/yyyy and the range search always worked correctly. That is until the year changed. After that it got really screwed up. Then I had to store them as yyyyddmm in order for it to work correctly.

 

[daniedb]

HI Guys

Here is the APP. Please Note. This is my First B4A App, so, any suggestion welcome

Cherio
D

 

[daniedb]

HI Guys

Here is the APP. Please Note. This is my First B4A App, so, any suggestion welcome

Cherio
D

Btw. It only designed for 320x480 Portrait. Haven't changed for other layouts yet

CHeers

 

[Mahares]

@daniedb: Congratulations. For a first app, it looks great.
I tested your apk application. I am still not convinced that formatting the date as dd/MM/yyyy as one of the database fields and running queries with a given date range will give you the correct result every time. I entered the following transactions for 5 June 2014, 13 july 2014 and 16 October 2014. When I ran your view statement for a date range 5 June through 16 October, the transaction of 13 july does not show up. Perhaps because my device is formatted for MM/dd/yyyy. But I would like you to test for yourself.
What I am really interested in is seeing your project or a miniature project as exported to zip file so I can see exactly how you coded it.
Vive L'Afrique du Sud

 

[daniedb]

Mahares
Thanks

' date lookup on Editing/Add New Transaction
Sub txdatebtn_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 Transaction Date", "Yes", "No", "Cancel", bmp)
   If ret = -1 Then
    txdate.Text=Dd.DayOfMonth&"/"&Dd.Month&"/"&Dd.year
   End If
End Sub

' statement search from/to date

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

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

'statement filter

Sub FilterStatement
Dim totalbalance As Double
stateview.Clear   'need to clear the list
Dim sqlquery As String
balance=0
   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

I use this code to format the field after lookup

txdate.Text=Dd.DayOfMonth&"/"&Dd.Month&"/"&Dd.year

I'll try your date searches and check the results

Cherio
Danie