SQL Server Portal

I came across this problem recently, when I was working on a report and I needed to truncate the date time till seconds. Given below are the two different methods we can use to achieve it.

Method 1 :
In this method, we will first convert the datetime value to varchar, truncate it and then convert it back to datetime data type using CONVERT function twice.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT
@datetime AS [Datetime with millisconds]
,CONVERT(DATETIME,CONVERT(VARCHAR(20),@datetime,120))
AS [Datetime without millisconds]
GO
--OUTPUT

Method 2 :
In this method, we will first FORMAT the datetime value to a defined format (truncate milliseconds) and then convert it back to datetime data type using CAST function.

--This script is compatible with SQL Server 2012 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT
@datetime AS [Datetime with millisconds]
,CAST(FORMAT(@datetime,'yyyy-MM-dd HH:mm:ss') AS datetime)
AS [Datetime without millisconds]
GO
--OUTPUT