CODEWITHSUNDEEP
sql-serverdatetime
Retrocoder
Retrocoder

Reputation: 4713

Ignoring the time element of a datetime field

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.

Upvotes: 0

Views: 2157

Answers (5)

Remus Rusanu
Remus Rusanu

Reputation: 294387

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

Upvotes: 1

brianary
brianary

Reputation: 9332

This is what datediff is for:

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

Upvotes: 0

Ilya Kochetov
Ilya Kochetov

Reputation: 18463

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

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

Upvotes: 1

Rippo
Rippo

Reputation: 22424

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))

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147304

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.

Upvotes: 3

Related Questions