SQL Server remove milliseconds from datetime
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...
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'