Why doesn't this function return a decimal?

Question

I want this function to take a date-time and return a time expressed as a decimal. EG. - 2:33 PM will be returned as 14.55

ALTER FUNCTION [dbo].[GetTimeAsDecimal](
@DateTime as datetime
) RETURNS decimal
AS
BEGIN
DECLARE @hour decimal
DECLARE @min decimal
DECLARE @result decimal
SELECT @hour = DATEPART(HOUR, @DateTime)
SELECT @min = (DATEPART(MINUTE, @DateTime)/60.0)
SELECT @result = @hour + @min 
RETURN @result
END

A similar query gives the expected results ...

SELECT DATEPART(HOUR, getDate()) + (DATEPART(MINUTE, getDate())/60.0)

Answer 1

It does returns decimal- but since you did not specify the accuracy and scale, the default scale is (number of digits after the decimal point) 0 ...... so you get a decimal number without any digits after the decimal point ... (so it will be rounded and may look as if it is not decimal - it is).

You need to change all your definitions for decimalso that ! - decimal(18,4) - .

decimal(18,4) :

• 18
• 4 (, , 14 )

- - decimal = decimal(18,0)

Answer 2

:

DECLARE @hour decimal(4,2)
DECLARE @min decimal(4,2)
DECLARE @result decimal(4,2)