SQL Server remove milliseconds from datetime

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

I'm very late but I had the same issue a few days ago. None of the solutions above worked or seemed fit. I just needed a timestamp without milliseconds so I converted to a string using Date_Format and then back to a date with Str_To_Date:

STR_TO_DATE(DATE_FORMAT(your-timestamp-here, '%Y-%m-%d %H:%i:%s'),'%Y-%m-%d %H:%i:%s')

Its a little messy but works like a charm.

Review this example:

declare @now datetimeoffset = sysdatetimeoffset();
select @now;
-- 1
select convert(datetimeoffset(0), @now, 120);
-- 2
select convert(datetimeoffset, convert(varchar, @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:

declare @myDateTimeValue = <date-time-value>
select cast(convert(varchar, @myDateValue, 120) as <same-type-as-@myDateTimeValue>);