CODEWITHSUNDEEP
sql-server-2008datetimewhere-clause
Abbas
Abbas

Reputation: 5044

how to pass the value for datetime field in where clause in sql server

hey guys, i have created a stored procedure, wherein i have datetime field in the select query. here is what my query is

select * from tablename where publishdate like '%03/10/2011%'

is this possible, or how should i form a query my objective is query should return the records for 10-March-2011

Upvotes: 0

Views: 4514

Answers (3)

George Mastros
George Mastros

Reputation: 24498

The best method for doing this is:

Select * 
from   tablename
Where  publishdate >= '20110310'
       and publishdate < '20110311'

By separating the conditions like this, you are allowing SQL Server to use an index on the publishdate column (if an index exists). The query I show would use a super-fast index seek instead of a scan, resulting in far better performance.

Upvotes: 0

FIre Panda
FIre Panda

Reputation: 6637

Try

select * from tablename where day(publishdate) = 10 And Month(publishdate) = 3
And Year(publishdate) = 2011 --Let the required date be 10th March 2011

Hope this help.

Upvotes: 0

Rodrigo
Rodrigo

Reputation: 4395

Using CONVERT

select * from tablename where convert(varchar(10), publishdate, 103) = '03/10/2011'

Upvotes: 1

Related Questions