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_Dat e" of Varchar(60) type.

the value, i am passing to Stored procedure through "Search_Dat e" 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]

Re: Date Compare in SQL Server Stored Procedure

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]

Re: Date Compare in SQL Server Stored Procedure

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]

Re: Date Compare in SQL Server Stored Procedure

David Portas (REMOVE_BEFORE_ REPLYING_dporta s@acm.org) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp