Date Compare in SQL Server Stored Procedure

[mhk]

Hi,

i have "req_date" column of "datetime" type in Database table besides
other columns.

From my Web page, i am calling the Stored Procedure with variable
parameter "Search_Date" of Varchar(60) type.

the value, i am passing to Stored procedure through "Search_Date" is
compared to req_date column of table.

My question is that how to do this comparision of date in WHERE part of
Select statement within Stored Procedure?

Thanks

[Simon Hayes]

Why are you using a varchar parameter when the correct data type is
datetime? I would fix the front end to use the proper data type, rather
than hack in something on the server side - it will also help to
prevent invalid data and SQL injection attacks.

If you really want to do it on the server, you can CAST() or CONVERT()
to datetime, but that's a workaround, not a solution.

Simon

[David Portas]

Use a DATETIME parameter rather than a VARCHAR otherwise you'll just
have to convert the value. Don't rely on implict conversion for date
strings because it is subject to regional settings on the server and
the connection.

Assuming @search_date is a DATETIME:

WHERE req_date = @search_date

or, if you just what to search on the date and ignore the time:

WHERE req_date >= @search_date AND req_date <
DATEADD(DAY,1,@search_date)

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

David Portas (RE****************************@acm.org) writes:

Use a DATETIME parameter rather than a VARCHAR otherwise you'll just
have to convert the value. Don't rely on implict conversion for date
strings because it is subject to regional settings on the server and
the connection.

It's worth pointing out here that the regional settings that David
are talking about are *not* those of Windows. Instead SQL Server
has its own settings that can affect how date literals are interpreted.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp