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:

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

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'

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

Use 'Smalldatetime' data type

select convert(smalldatetime, getdate())

will fetch

2015-01-08 15:27:00

There's more than one way to do it:

select 1 where datediff(second, '2010-07-20 03:21:52', '2010-07-20 03:21:52.577') >= 0

or

select *
from table
where datediff(second, '2010-07-20 03:21:52', date) >= 0 

one less function call, but you have to be beware of overflowing the max integer if the dates are too far apart.

One more way I've set up SQL Server queries to ignore milliseconds when I'm looking for events from a particular second (in a parameter in "YYYY-MM-DD HH:TT:SS" format) using a stored procedure:

  WHERE 
  ...[Time_stamp] >= CAST(CONCAT(@YYYYMMDDHHTTSS,'.000') as DateTime) AND 
  ...[Time_stamp] <= CAST(CONCAT(@YYYYMMDDHHTTSS,'.999') as DateTime) 

You could use something similar to ignore minutes and seconds too.

Please try this

select substring('12:20:19.8470000',1,(CHARINDEX('.','12:20:19.8470000',1)-1))


(No column name)
12:20:19

You could also convert datetime to varchar style 120 and then back to datetime. I did not find any similar answer like this so I thought I would post

Select Cast(Convert(varchar, getdate(), 120) AS datetime)

which will result in

2022-04-08 10:16:03.000

May be this will help.. SELECT [Datetime] = CAST('20120228' AS smalldatetime)

o/p: 2012-02-28 00:00:00

Review this example:

declare @now datetimeoffset = sysdatetimeoffset();
select @now;
-- 1
select convert(datetimeoffset(0), @now, 120);
-- 2
select convert(datetimeoffset, convert(varchar(max), @now, 120));

which yields output like the following:

2021-07-30 09:21:37.7000000 +00:00
-- 1
2021-07-30 09:21:38 +00:00
-- 2
2021-07-30 09:21:37.0000000 +00:00

Note that for (1), the result is rounded (up in this case), while for (2) it is truncated.

Therefore, if you want to truncate the milliseconds off a date(time)-type value as per the question, you must use:

select convert(datetimeoffset, convert(varchar(max), @myDateValue, 120));