Omitting the Milliseconds in a Date

Use DATETIME2, a new datatype in SQL Server 2008 that supports fractional precision:

SELECT  CONVERT(DATETIME2(0),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss], CONVERT(DATETIME2(1),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss.f], CONVERT(DATETIME2(2),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss.ff], CONVERT(DATETIME2(3),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss.fff], CONVERT(DATETIME2(4),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss.ffff], CONVERT(DATETIME2(5),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss.fffff], CONVERT(DATETIME2(6),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss.ffffff], CONVERT(DATETIME2(7),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss.fffffff]

The conversion will round to the nearest unit, eg:

2014-09-04 09:35:47.0162993 as DATETIME2(4) -> 2014-09-04 09:35:47.0163

Alternatively, on SQL 2005 and eariler:

SELECT  original  = GETDATE(), [floor]   = DATEADD(ms,-DATEPART(ms,GETDATE()),GETDATE()), [ceiling] = DATEADD(ms,1000-DATEPART(ms,GETDATE()),GETDATE()), [rounded] = DATEADD(ms,CASE WHEN DATEPART(ms,GETDATE()) < 500 THEN 0 ELSE 1000 END-DATEPART(ms,GETDATE()),GETDATE())

This is a bit faster than converting to and from a string representation.

Use:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(19), GETDATE(), 120))

This:

CONVERT(VARCHAR(19), GETDATE(), 120)

...omits the milliseconds, returning a VARCHAR. So you CAST/CONVERT that into a DATETIME in order to work with the desired data type.

See this link for a list of various date/time formats you can work with.

If you don't want to use string conversions, here's a solution:

DECLARE @TheDate datetime, @Today datetimeSET @TheDate = GetDate()SET @Today = DateAdd(dd, DateDiff(dd, 0, @TheDate), 0)SELECT DateAdd(s, DateDiff(s, @Today, @TheDate), @Today)