SQL Server remove milliseconds from datetime

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?

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'

If you are using SQL Server (starting with 2008), choose one of this:

CONVERT(DATETIME2(0), YourDateField)
LEFT(RTRIM(CONVERT(DATETIMEOFFSET, YourDateField)), 19)
CONVERT(DATETIMEOFFSET(0), YourDateField) -- with the addition of a time zone offset

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

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:

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'

Related