[RESOLVED] How to select like on a parametized sqlcecommand?

Hello guys, im been doing great with parameters thanks to some people on this forum suggestions but now it semms that i am finally stuck with an issue, i have into a database a table which countains a column which is datebased, the date is date and time.
Now i am trying to search based on the day only, so it will filter all rows with the date from the date & time column, example:

Code:

---

id|    datestarted  |summary            |isdeleted|
1 |18-10-2001 17:30|it started out great...|0          |
2 |21-11-2011 17:30|had some issues...    |0          |

---

now i tried:

Code:

---

maskedtextbox1.text="18-10-2011";
sqlcecommand.commandtext = "SELECT * FROM TABLE1 WHERE IsDeleted=0 AND datestarted LIKE @datestarted";
sqlcecommand.parameters.addwithvalues("@datestarted",maskedtextbox1.text +"%");

---

however this doesnt work by not returning any error at all and never finding any match, how can i fix this? ive searched around the net and all i could find was non parametized commands, any help regarding this matter will be appreciated as usual, thanks for your time taken to read this thread and thanks in advance for any reply

Moved From The CodeBank (which is for sharing code rather than posting questions :) )

Quote:

---

Originally Posted by Hack

Moved From The CodeBank (which is for sharing code rather than posting questions :) )

---

im very sorry for the mistake :\ and thank you for moving the thread ^^

You can't use LIKE with any data type other than text. You can only use things like =, < and > for dates. In this situation you have two choices:

1. Use a database function to remove the time from the date/time column value.
2. Use two date parameters a day apart.

The first option would look something like this:

SQL Code:

WHERE DateFunction(MyDateTimeColumn) = @MyDateParameter

There is no actual DateFunction function. You'd have to check the documentation for your database to see if a function that does that exists.

Here's an example of the second option to get all records with yesterday's date:

csharp Code:

myCommand.CommandText = "SELECT * FROM MyTable WHERE MyDateTimeColumn >= @StartDate AND MyDateTimeColumn < @EndDate";
myCommand.Parameters.AddWithValue("@StartDate", DateTime.Today.AddDays(-1))
myCommand.Parameters.AddWithValue("@EndDate", DateTime.Today)

You could probably also use BETWEEN but I can never remember which limits are inclusive and which are exclusive.

Quote:

---

Originally Posted by jmcilhinney

You can't use LIKE with any data type other than text. You can only use things like =, < and > for dates. In this situation you have two choices:

1. Use a database function to remove the time from the date/time column value.
2. Use two date parameters a day apart.

The first option would look something like this:

SQL Code:

WHERE DateFunction(MyDateTimeColumn) = @MyDateParameter

There is no actual DateFunction function. You'd have to check the documentation for your database to see if a function that does that exists.

Here's an example of the second option to get all records with yesterday's date:

csharp Code:

myCommand.CommandText = "SELECT * FROM MyTable WHERE MyDateTimeColumn >= @StartDate AND MyDateTimeColumn < @EndDate";
myCommand.Parameters.AddWithValue("@StartDate", DateTime.Today.AddDays(-1))
myCommand.Parameters.AddWithValue("@EndDate", DateTime.Today)

You could probably also use BETWEEN but I can never remember which limits are inclusive and which are exclusive.

---

Even better than what i wanted, thank you so much, i cant semm to add rep right now but as soon as i "spread" enough rep ill add it asap.
Thanks again