Why are you taking a DateTime value, and converting it to a DateTime, then converting it to Text via other DateTime values you subsequently ignore?
date1.Value = Convert.ToDateTime(date1.Value.Date)
date2.Value = Convert.ToDateTime(date2.Value.Date)
d1 = date1.Value
d2 = date2.Value
Then you convert that to text to concatenate strings to form an SQL command - which is a bad idea at the best of times - and write bad SQL to hold it?
So ... first off: Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.
When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.
So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?
Secondly: the SQL syntax for BETWEEN is nothing like what you have there:
SQL BETWEEN Operator[
^]
Thirdly, pass the DateTime.Date value directly to the DB via a parameter and there is no need for text at either end, so no possibility for misinterpreting date values: is 01/02/03 the 1st Feb 2003, 2nd Jan 2003, or 3rd Feb 2001? Pass it as a DateTime and teh DB already knows what the user wanted!