February 2010 Tip of the Month

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

Both comments and trackbacks are currently closed.
%d bloggers like this: