Ignoring the time element of a datetime field

Question

I have a datetime field in my table. I want to delete records based on a date but I am not interested in the time element. How would I write the SQL for this ? I am using MS SQL 2008.

Answer 1

For best use of indexes, I'd go for this kind of approach:

To delete all records for 1st December:

DECLARE @DateToDelete DATETIME
SET @DateToDelete = '20091201' 

DELETE FROM MyTable
WHERE MyDateField >= @DateToDelete AND MyDateField < DATEADD(dd, 1, @DateToDelete)

The alternatives include:

DELETE FROM MyTable
WHERE CAST(CONVERT(VARCHAR(10), MyDateField, 120) AS DATETIME) = @DateToDelete

which converts each datetime value to just it's date part.

But I'd still go with my original way as it allows for more efficient execution.

Answer 2

Try this:-

declare @date datetime 
set @date = '2006-11-09'
select @date, dateadd(ms, -1, DATEADD(dd,1,@date))
delete from login 
where datecreated between @date AND dateadd(ms, -1, DATEADD(dd,1,@date))

Answer 3

If you use MS SQL 2008 then you could convert to a new DATE type

DELETE FROM table WHERE date_filed >= CONVERT(DATE,GETDATE())

Answer 4

This is what datediff is for:

delete from Table where datediff(day,'2009-12-09',date_filled) = 0

Answer 5

Is the time relevant in any other place? If not, then you should use a DATE column instead. If you cannot, then the best way to seek a date part of a datetime in a WHERE clause is to use a range:

... WHERE dateColumn BETWEEN '20091221' and '20091222';

Note that given the datetime accuracy of 3ms a datetime like 20091221 23:59:59.999 may be aproximated to 20091222 00:00:00.000 and this can sometime create problems.

There is a great collection of blog posts on the topic of datetime at T-SQL Tuesday #001 (Date/Time Tricks): The Roundup