Monday, August 29, 2011

Rounding date in SQL query


Query :
SELECT
DATEADD( MI, DATEDIFF( MI, 0, DATEADD( SS, 30, Dates.SampleDate) ), 0) AS RoundedDate
FROM
(
SELECT SampleDate = CONVERT( DATETIME, '8/29/2011 12:59:29.998')
UNION ALL
SELECT SampleDate = CONVERT( DATETIME, '8/29/2011 12:59:30.000')
) Dates

Results :

RoundedDate
-----------------------
2011-08-29 12:59:00.000
2011-08-29 13:00:00.000