Truncate Datetime to Second (Remove Milliseconds)

问题:

What is the best way to shorten a datetime that includes milliseconds to only have the second?

For example 2012-01-25 17:24:05.784 to 2012-01-25 17:24:05

回答1:

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

or

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

CAST and CONVERT
DATEADD
DATEPART

回答2:

The fastest, also language safe and deterministic

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

回答3:

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

回答4:

so, the easiest way now is:

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

回答5:

The following has very fast performance, but it not only removes millisecond but also rounds to minute. See (http://msdn.microsoft.com/en-us/library/bb677243.aspx)

select cast(yourdate as smalldatetime) from yourtable

Edit:

The following script is made to compare the scripts from Mikael and gbn I upvoted them both since both answers are great. The test will show that gbn' script is slightly faster than Mikaels:

declare @a datetime
declare @x int = 1 
declare @mikaelend datetime

declare @mikael datetime = getdate() 
while @x < 5000000 
begin   
  select @a = dateadd(millisecond, -datepart(millisecond, getdate()), getdate()) , @x +=1 
end  
set @mikaelend = getdate()

set @x = 1 
declare @gbnend datetime
declare @gbn datetime = getdate() 
while @x < 5000000
begin 
  select @a = DATEADD(second, DATEDIFF(second, '20000101', getdate()), '20000101')  , @x +=1 
end  
set @gbnend = getdate()
select datediff(ms, @mikael, @mikaelend) mikael, datediff(ms, @gbn, @gbnend) gbn 

First run

mikael      gbn
----------- -----------
5320        4686

Second run

mikael      gbn
----------- -----------
5286        4883

Third run

mikael      gbn
----------- -----------
5346        4620