Jeff Maass Blog

I use this function for testing. It takes in milliseconds and spits out the whole number value in various other rulers (seconds, minutes, etc).

USE MaasSql

GO

USE [MaasSql]
GO
/****** Object:  UserDefinedFunction [cCode].[DateTime_GetUtcOffset]    Script Date: 02/25/2008 07:01:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cCode].[MilliSeconds_ToDetail]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
    BEGIN
        DROP FUNCTION cCode.MilliSeconds_ToDetail
    END

GO

CREATE FUNCTION cCode.MilliSeconds_ToDetail ( @Milliseconds BIGINT )
RETURNS @Results TABLE
    (
      ID INTEGER IDENTITY(1 , 1)
                 PRIMARY KEY
    , milliseconds BIGINT
    , Seconds REAL
    , Minutes REAL
    , Hours REAL
    , Days REAL
    , Weeks REAL
    )
AS 
BEGIN

    DECLARE @MilliSecondsREAL AS REAL ;

    SET @MilliSecondsREAL = @Milliseconds ;

    DECLARE @SecondsDivisor AS REAL ;

    SET @SecondsDivisor = 1000 ;

    DECLARE @MinutesDivisor AS REAL ;

    SET @MinutesDivisor = @SecondsDivisor * 60 ;

    DECLARE @HoursDivisor AS REAL ;

    SET @HoursDivisor = @MinutesDivisor * 60 ;

    DECLARE @DaysDivisor AS REAL ;

    SET @DaysDivisor = @HoursDivisor * 24 ;

    DECLARE @WeeksDivisor AS REAL ;

    SET @WeeksDivisor = @DaysDivisor * 7 ;

    INSERT INTO
        @Results
        (
          milliseconds
        , Seconds
        , Minutes
        , Hours
        , Days
        , Weeks

        )
        SELECT
            @MilliSecondsREAL
        ,   @MilliSecondsREAL / @SecondsDivisor seconds
        ,   @MilliSecondsREAL / @MinutesDivisor minutes
        ,   @MilliSecondsREAL / @HoursDivisor hours
        ,   @MilliSecondsREAL / @DaysDivisor days
        ,   @MilliSecondsREAL / @WeeksDivisor weeks

    RETURN

END

GO