SQLite Search Between Dates

Databases
1

Hi,

I am trying to get all the records stored in a Table between two dates. The date values are listed in two textboxes called txtDate1 and txtDate2. The dates are stored in the SQLite table in the format of… 2013-08-28. The code I am using is listed below but no records are returned in the query even though records are recorded between these dates.

Dim sqlook as String sqlook="SELECT * FROM MeetingAT WHERE UPPER(Date) Between '" + uppercase(txtDate1.Text) + "' And UPPER(Date) = '" + uppercase(txtDate2.Text) + "'" rs=Roster.SQLSelect(sqlook) txtMeetings.Text= format(rs.RecordCount,"0")

Can anyone see what may be wrong with my code? Any help would be greatly appreciated.

Jim

2

Firstly, why are you using ‘UPPER’ on a date field? It should only include numbers, no need to convert to uppercase

Also, how is the text entered into the 2 date fields? Are they also in the 2013-08-28 format?

Then, examine your query string after it is populated from the field values. It looks like your query reads something like

Why do you have the ‘Date =’ after the ‘Between’?

3

The correct SQL syntax is

select a from b where date between x and y

As the x and y parameters are dates they should be shown as ‘2013-08-13’ with the quotes surrounding the date.

Your problem is the SQL syntax, I’m sure.

4

Thank you very much Mark and Simon. The syntax error was the problem. The correct way to write the line was:

sqlook="SELECT * FROM MeetingAT WHERE Date Between  '" + uppercase(txtDate1.Text) + "' And   '" + uppercase(txtDate2.Text) + "'"

That works great. I appreciate you taking the time to help me.

Jim

5

I don’t think you need the uppercase statements. You also need to ensure that the dates in the text fields is formatted correctly before blindly using them.

6

Your right Simon, you don’t need the uppercase in there. It works fine without it. Thank you again.

7

FWIW, when searching a datetime field in a MySQL database I append a time parameter like so:

sDat = "t.created_on BETWEEN '" + srchStartDate.SQLDate + " 00:00:00.000' AND '" sDat = sDat + srchEndDate.SQLDate + " 23:59.59.997'" // *** note manually inserted times above to ensure we get full results

You can inadvertently omit results if you forget this. Don’t know if it applies to SQLIte, but I’ll post it here for (my…) future reference.