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 Rdid you try
01/02/2005 in the query or just the single digit?That is given by a user. He may enter as single digit/double digit.
SivaSELECT * 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 & "'"
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 Rdid you try
01/02/2005 in the query or just the single digit?That is given by a user. He may enter as single digit/double digit.
SivaSELECT * 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 & "'"