Date Conversion

I'm searching on a smalldatetime field in SQL Server so a typical value would be 09/21/2005 11:30:00 AM. I have a search form which offers the user a textbox to search by date and unless they enter the exact date and time, no matching records are found. Of course I want I all records for a given day to be returned. This is how I'm doing it now. Thanks.

Dim dteDate_RequestedAsString = txtDate_Requested.Text

If dteDate_Requested <>""Then
strSqlText +=" Date_Requested='" & dteDate_Requested &"'"
EndIf

You have to change to DateTime to get the results you want because SmallDateTime have limited resolution. Try the link below for more info. Hope this helps.
http://www.stanford.edu/~bsuter/sql-datecomputations.html|||I've changed my SQL Server field type from SmallDateTime to DateTime and it's still not working. If I do a response.write on the SQL statement, I see that it's working correctly (WHERE Date_Requested='09/22/2005')|||You need to keep in mind that there is no Date data type. All ofthe data types involving dates also includes times. You need towind up with a query that looks like this:

WHERE Date_Requested >= '20050922' AND Date_Requested < '20050923'

That is my best recommendation. That will return you all recordswhere Date_Requested falls on 9/22/2005 regardless of the time part ofthe date you are storing.
I must strongly recommend to you that you use Parameters instead of concatenating UI-supplied data to a string to be executed.
Here's the why:

Please, please, please, learn about injection attacks!
How To: Protect From SQL Injection in ASP.NET

And here's the how:

Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2

|||I see. I've been a developer quite awhile and did not know this was the best way to search date fields.
This is for a small intranet app so I'm not concerned about SQL injection attacks in this case but that is very good advice.
Thanks for the help.
|||

evanburen wrote:

I see. I've been a developer quite awhile anddid not know this was the best way to search date fields.

It's just what I've learned through trial and error and seeing otherpeople struggling with it. The method I suggested will takeadvantage of any index on your date field, and it takes the time partof the date out of the equation.

evanburen wrote:

This isfor a small intranet app so I'm not concerned about SQL injectionattacks in this case but that is very good advice.

I have a few thoughts to offer on this viewpoint.
While one would like to think that all coworkers are trustworthy,a curious or disgruntled employee, or perhaps a temporary contractor,might try to access data to which they are not otherwise privileged, orperhaps even attempt to inflict damage to the database ornetwork. There still might be data which needs to be keptsafeguarded, such as payroll information, benefit information.