Select records for a given date from database

[rskshiva]

Hai,

I need to select/update/delete the records for the date given by the user.
The user gives the date as 1/5/2005 (mm/dd/yyyy format).
As the field is datetime the vales is as like "1/1/2005 6:59:37 PM".
When I wrote a query like " select *from test where testdate='1/5/2005' " it doesn't took any records.

I had tried
CAST(CONVERT(Varchar, SentDateTime, 101) AS DateTime) = '" & strDate & "'"

It is returnig the correct rows.

But it will make problems after indexing.

Is there any better way to to this?

Suggestions are most welcome.

Regards,
Siva R

[PeOfEo]

did you try
01/02/2005 in the query or just the single digit?

[rskshiva]

That is given by a user. He may enter as single digit/double digit.

Siva

[Cstick]

SELECT * FROM test WHERE datepart(day,indate) = '09'and datepart(month,indate) = '1' and datepart(year,indate) = '2005'


I'm kind of confused by what you mean by indexing, but the above should work if the user enters single/double digit for month and day, however the year has to be 4 digit. I think there is an easier way and that I am using it in an app at work right now, I'll take a look and see.

I would like to mention that it is more secure to use parameters in your SQL queries, like this:


dim datenow as datetime = datetime.now

myCmd = new SQLCommand("SELECT * FROM test WHERE date = @date", myConnection)

myCmd.Parameters.Add("@date",datenow)

myCmd.ExecuteNonQuery()


Using parameters prevents SQL injections by preventing data in parameters from being executed. I'm mentioning this since in your example you do this:


'" & strDate & "'"