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?

Solution 1

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'

Solution 2

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

Solution 3

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

Solution 4

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)

Solution 5

Use CAST with following parameters:

Date

select Cast('2017-10-11 14:38:50.540' as date)

Output: 2017-10-11

Datetime

select Cast('2017-10-11 14:38:50.540' as datetime)

Output: 2017-10-11 14:38:50.540

SmallDatetime

select Cast('2017-10-11 14:38:50.540' as smalldatetime)

Output: 2017-10-11 14:39:00

Note this method rounds to whole minutes (so you lose the seconds as well as the milliseconds)

DatetimeOffset

select Cast('2017-10-11 14:38:50.540' as datetimeoffset)

Output: 2017-10-11 14:38:50.5400000 +00:00

Datetime2

select Cast('2017-10-11 14:38:50.540' as datetime2)

Output: 2017-10-11 14:38:50.5400000

View more solutions

Comments

[vacip]

datetime2 is the best (least expensive) solution IMO

[ZygD]

@vacip: in 2010 there would have been less SQL Server 2008+ and more SQL Server 2005 which did not have datetime2

[Kon]

CONVERT(DATETIME, CONVERT(VARCHAR(20), @date, 120)) is a great solution for comparing DateTime values while ignoring milliseconds. Thanks.

[jstuardo]

this truncates also seconds which is not what asker wants.

[MMJ]

It is very likely that the date value is not entered manually, but comes from some variable containing the date, which will be inserted in the query. Therefore, you would need to use functions anyway, as it would be necessary to add 1 second to the time used in the WHERE clause, using for example the function DATEADD(second, 1, date). ;)

[Matt Kemp]

The DATETIME2 option rounds up if > 0.5, the LEFT(RTRIM option truncates. Both are useful

[Northernlad]

In my experience datetime2(0) will round up the "2020-07-01 15:16:39.837" to "2020-07-01 15:16:40", so is not the best option.

[DataMan]

Thank you. Nice and clean way

[tymtam]

Please note that casting to datetime2(0) will round, not trim.

[tymtam]

Please mind that this literally removes milliseconds, it does not remove the smaller units, e.g. For '2021-06-14 00:00:00.1234567' select DATEADD(ms, -DATEPART(ms, date), date) produces 2021-06-14 00:00:00.0004567

[matt123788]

VARCHAR(20) was throwing a conversion error for me but I found using VARCHAR(19) instead did the trick