Technical Article

Make a time value from a given float value

,

Clients asked me to show a more detailed and longer text which doesn't have to be edited. So I came up with the following solution/function.
This function returns a varchar time value with a description containing how many days, hours and minutes. For example 100.25 returns :
'100:15( 4 Days, 4 Hours, 15 Minutes )'.
I hope somebody can use it or it was helpfull to make things easier.
Thanks in advance.
Paul

CREATE     FUNCTION dbo.ReturnValidTimeInclDescription (@TIME DECIMAL (20,2))
   RETURNS VARCHAR(50)
AS BEGIN
  DECLARE @VALUE DECIMAL (20,2),
          @RETURNVALUE VARCHAR(50)
  SET @VALUE = @TIME 
  SET @RETURNVALUE = 
    CASE WHEN CAST(FLOOR(@VALUE) AS INTEGER) > 9 AND 
              CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) > 9 THEN
      CAST(CAST(FLOOR(@VALUE) AS INTEGER) AS VARCHAR) + ':' +
      CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) +
      '( ' + CAST(CAST(ROUND(FLOOR(@VALUE/24),0) AS INTEGER) AS VARCHAR) + ' Days, ' + 
             CAST(CAST(ROUND(FLOOR(@VALUE) - (FLOOR(@VALUE/24) * 24),0) AS INTEGER) AS VARCHAR) + ' Hours, ' +
             CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) + ' Minutes )'
    ELSE
      CASE WHEN CAST(FLOOR(@VALUE) AS INTEGER) > 9 AND 
                CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) < 10 THEN
        CAST(CAST(FLOOR(@VALUE) AS INTEGER) AS VARCHAR) + ':0' +
        CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) +
        '( ' + CAST(CAST(ROUND(FLOOR(@VALUE/24),0) AS INTEGER) AS VARCHAR) + ' Days, ' + 
               CAST(CAST(ROUND(FLOOR(@VALUE) - (FLOOR(@VALUE/24) * 24),0) AS INTEGER) AS VARCHAR) + ' Hours, ' +
               CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) + ' Minutes )'
      ELSE
        CASE WHEN CAST(FLOOR(@VALUE) AS INTEGER) < 10 AND 
                  CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) > 9 THEN
          '0' + CAST(CAST(FLOOR(@VALUE) AS INTEGER) AS VARCHAR) + ':' +
          CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) +
          '( ' + CAST(CAST(ROUND(FLOOR(@VALUE/24),0) AS INTEGER) AS VARCHAR) + ' Days, ' + 
                 CAST(CAST(ROUND(FLOOR(@VALUE) - (FLOOR(@VALUE/24) * 24),0) AS INTEGER) AS VARCHAR) + ' Hours, ' +
                 CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) + ' Minutes )'
        ELSE 
          CASE WHEN CAST(FLOOR(@VALUE) AS INTEGER) < 10 AND 
                    CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) < 10 THEN
            '0' + CAST(CAST(FLOOR(@VALUE) AS INTEGER) AS VARCHAR) + ':0' +
            CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) +
            '( ' + CAST(CAST(ROUND(FLOOR(@VALUE/24),0) AS INTEGER) AS VARCHAR) + ' Days, ' + 
                   CAST(CAST(ROUND(FLOOR(@VALUE) - (FLOOR(@VALUE/24) * 24),0) AS INTEGER) AS VARCHAR) + ' Hours, ' +
                   CAST(CAST(ROUND(60 * ((@VALUE - FLOOR(@VALUE))),0) AS INTEGER) AS VARCHAR) + ' Minutes )'
          ELSE
            '( Unassignend value)'
          END
        END
      END
    END
  RETURN @RETURNVALUE
END

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating