CREATE FUNCTION dbo.fnRoundTimeToNearestSecond (@TimeIn DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(ms,ROUND(DATEDIFF(ms,DATEADD(day,DATEDIFF(day,0,@TimeIn),0),@TimeIn ),-3),DATEADD(day,DATEDIFF(day,0,@TimeIn),0))
END
GO
-------------------------------------------------
CREATE FUNCTION dbo.fnRoundTimeToNearestMinute (@TimeIn DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN dbo.fnRoundTimeToMinIncrement(@TimeIn, 1)
END
GO
------------------------------------------------
CREATE FUNCTION dbo.fnTruncateTimeToNearestHour (@TimeIn DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(Hour, DATEDIFF(Hour, 0, @TimeIn), 0)
END
GO
------------------------------------------------
CREATE FUNCTION dbo.fnTruncateTimeToNearestDay (@TimeIn DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(Day, DATEDIFF(Day, 0, @TimeIn), 0)
END
GO
------------------------------------------------
CREATE FUNCTION dbo.fnTruncateTimeToNearestMonth (@TimeIn DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(Month, DATEDIFF(Month, 0, @TimeIn), 0)
END
GO
------------------------------------------------
CREATE FUNCTION dbo.fnTruncateTimeToNearestYear (@TimeIn DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(Year, DATEDIFF(Year, 0, @TimeIn), 0)
END
GO
------------------------------------------------
CREATE FUNCTION dbo.fnRoundTimeToMinIncrement
(
@TIME DATETIME
, @MIN SMALLINT
)
RETURNS DATETIME
AS
BEGIN
SET @MIN = ABS(@MIN) -- Ensure minutes is a positive number
IF @MIN > 1440
SET @MIN = 1440
-- We are going to separate the DATE and TIME parts, do some math and add them together
RETURN DATEADD(DAY, 0, DATEDIFF(DAY, 0, @TIME)) + -- Here's the DATE part
CAST(ROUND(CAST(CAST(CONVERT(VARCHAR(12), @TIME, 14) AS DATETIME) AS FLOAT) * 1440.0 / @MIN, 0) / (1440.0 / @MIN) AS DATETIME) -- And now the TIME math
END
GO
__________________________________________________________________ 
SQL Server T-SQL: Working with time values in queries has always presented challenges, especially when you need only part of a datetime value. Either truncating or rounding time has usually been done by converting a datetime value to a string and then using the parts that are needed. The functions below can be used to return time in several different states. Use them to round or truncate time values.
