How Do You Include a Date Range in a SQL Statement

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

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.

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

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

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

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