why does this function not return decimal?

Question

I want this function to take a datetime and return the time expressed as a decimal. E.G. - 2:33 PM would 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 produces the results expected...

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

Answer 1

Try changing your declarations as follows:

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

Answer 2

It does return a decimal - but since you didn't specify a precision and scale, it default to a scale (number of digits after the decimal point) of 0...... so you get a decimal without any digits after the decimal point... (so it'll be rounded and might look like it's not really a decimal - it is).

You need to change all your definitions for decimal to something that does include a scale! Something like decimal(18,4) or something.

A definition of decimal(18,4) means:

• a total of 18 digits
• of which 4 digits are after the decimal point (and thus 14 before that)

The default - if you don't specify anything else - is decimal = decimal(18,0)