Change time of a datetime attribute

I have this query:

select id from table where date >= @idate

I want开发者_如何转开发 to set the time of the date attribute in the where = 00:00

How can I do it?

Best to use range scans. Why do you need to change the value stored in the column? If you want to find everything that happened on 2009-09-15 (regardless of time), you can say:

WHERE [date] >= '20090915'
AND [date] < '20090916';

Now you will still be able to use an index on the [date] column, if it exists, which it arguably should if you are running queries like this often. Converting on the left hand side leads to non-SARGable queries which will almost unilaterally suck performance-wise.

A useful link to check out is Tibor's article on date/time data types, including querying tips:

http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes

I also wrote a pretty lengthy article on avoiding bad practices when querying date ranges:

• Bad habits to kick : mis-handling date / range queries

e.g.

CAST(CONVERT(VARCHAR(10), date, 120) AS DATETIME)

Note that this will not make good use of any index on the date field, and so you may see poor performance especially with higher data volumes.

Alternatives would be to:
- re-evaluate what you're trying to achieve and how
- store the date and time elements of the field in 2 separate, indexable fields.

You should never change the column information unless absolutely necessary - this causes a full table scan and ignores indexes. Just make sure @idate is correct.