How Do You Include a Date Range in a SQL Statement

[Mahares]

I have an SQLite table that has among its few fields, one field called DATE defined as a TEXT field and formatted as: M/dd/YYYY in the table. I would like to export a portion of the table data to a text file using a date range. The START and END dates are entered in 2 textedit views. I would like to use a SQL statement such as:
SELECT * FROM MyTable WHERE DATE between StartDate.text and EndDate.text
I also tried: SELECT * FROM MyTable WHERE DATE >= StartDate.text and DATE <= EndDate.text
I have been struggling with this for more than 2 days, although I can do this in Microsoft Access in seconds. I tried to include julianday, datetime.Dateparse in the SELECT statement, but it crashes with every combination. I hope someone has done it and can shed some light on this big dilemma. I am finding out SQLite and B4A do not mix very well. Here is a sample of similar data:
ITEM DATE DEPTH COMMENT
1456 1/2/2012 56 Stable
2767A 12/30/2011 34 Erratic
5623 1/7/2012 109 exceeds limit
1456 1/10/2012 76 improving
Thank you

 

[timwil]

When I store dates in my database I usually save it as YYYYMMDDHHMMSS - this way the data will line up in date order automatically.

 

[Mahares]

Unfortunately, I am stuck with this database where the table in question has thousands of records and feeds into a much larger database. The DATE field is formatted as M/dd/yyy and the TIME field which is separate as hh:mm:ss a. Both are text. I have to find a way to do it.

 

[timwil]

the reason you can do it in MSAccess is that the field is an actual date field but here you are dealing with a text field.

Is the database static? Where do the records come from?

If it is static then you can add another field and do a once thru to update that field with a workable date format.

If the records come from an import then you have the opportunity to add a field in a workable format

 

[Mahares]

Are you telling me there is no function or an expression that I can put inside the SQL statement that adds another calculated field that say, converts the date into YYYYMMDD and puts it in a column as part of the query, but not to be saved as a new field in the table, so I can use it simply to select the date range? I am looking for something in this context:

SELECT ITEM, DATE, DEPTH, COMMENT, SORTDATE(DATE) as SRT_DATE FROM MyTable WHERE SRT_DATE >= StartDate.text and SRT_DATE <= EndDate.text

Here SORTDATE would be a function that converts the DATE from M/DD/YYY to YYYYMMDD as shown BELOW:

SUB SORTDATE(DATE as string)
Dim MyDate,MyDate2, M, D, Y ,SortDate As String
M=MyDate.SubString2(0,MyDate.IndexOf("/")) 'returns 1 in 1/10/2012
MyDate2=MyDate.Substring(M.Length+1) 'returns 10/2011 in 1/10/2012
D=MyDate2.SubString2(0,MyDate2.IndexOf("/")) 'returns 10 in 1/10/2012
Y=MyDate.SubString(MyDate.Length-4) 'returns the year 4 digits 2012 in 1/10/2012
If M.Length=1 Then
M="0" & M 'to make it 2 digits month
End If
If D.Length=1 Then
D="0" & D 'to make it 2 digits day
End If
SORTDATE =Y&M&D
END SUB

 

[timwil]

Check out

SQLite UDFs: Adding Sqrt and CToF Functions

apparently you can do User Defined Functions in SQLLite - do a google search - I am not sure what all is supported in the Android version though

Good Luck

 

[Mahares]

The link did not do me much good . I will keep working at it. I am optimistic that someone else comes up with another option that prevents adding a new field to a database that has been stable. If not, I will create a new field as you suggested that has the following fomat: YYYYMMDD and use it for the WHERE clause and hope it will work.
Thank you for your efforts