Truncate Datetime to Second (Remove Milliseconds) in T-SQL

This will truncate the milliseconds.

declare @X datetime
set @X = '2012-01-25 17:24:05.784'
select convert(datetime, convert(char(19), @X, 126))

select dateadd(millisecond, -datepart(millisecond, @X), @X)

CAST and CONVERT
DATEADD
DATEPART

convert(datetime, convert(varchar, @datetime_var, 120), 120)

The fastest, also language safe and deterministic

DATEADD(second, DATEDIFF(second, '20000101', getdate()), '20000101')

The easiest way now is:

select convert(datetime2(0) , getdate())

Related