February 2010 Tip of the Month

By SQLRx Admin | Helpful Scripts

May 28

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

>