Change time of a datetime attribute

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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://www.karaszi.com/SQLServer/info_datetime.asp

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

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx