SQL Server remove milliseconds from datetime

Question

select *
from table
where date > '2010-07-20 03:21:52'

which I would expect to not give me any results... EXCEPT I'm getting a record with a datetime of 2010-07-20 03:21:52.577

how can I make the query ignore milliseconds?

Answer 1

select * from table
     where DATEADD(ms, DATEDIFF(ms, '20000101', date), '20000101') > '2010-07-20 03:21:52'

You'll have to trim milliseconds before comparison, which will be slow over many rows

Do one of these to fix this:

• created a computed column with the expressions above to compare against
• remove milliseconds on insert/update to avoid the read overhead
• If SQL Server 2008, use datetime2(0)

Answer 2

Try:

SELECT * 
FROM table 
WHERE datetime > 
CONVERT(DATETIME, 
CONVERT(VARCHAR(20), 
CONVERT(DATETIME, '2010-07-20 03:21:52'), 120))

Or if your date is an actual datetime value:

DECLARE @date DATETIME
SET @date = GETDATE()
SELECT CONVERT(DATETIME, CONVERT(VARCHAR(20), @date, 120))

The conversion to style 120 cuts off the milliseconds...

Answer 3

You just have to figure out the millisecond part of the date and subtract it out before comparison, like this:

select * 
from table 
where DATEADD(ms, -DATEPART(ms, date), date) > '2010-07-20 03:21:52'

Answer 4

For this particular query, why make expensive function calls for each row when you could just ask for values starting at the next higher second:

select *
from table
where date >= '2010-07-20 03:21:53'