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