为什么这个函数不返回十进制？

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

我希望此函数采用日期时间并返回以小数表示的时间。例如。 - 下午2:33将返回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)

2 个解决方案

#1

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).

它确实返回一个小数 - 但由于你没有指定精度和标度,它默认为一个标度(小数点后的位数)为0 ......所以你得到一个小数,后面没有任何数字小数点...(因此它将被舍入并且可能看起来它不是真正的小数 - 它是)。

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

您需要将所有十进制定义更改为包含比例的内容!像十进制(18,4)之类的东西。

A definition of decimal(18,4) means:

十进制(18,4)的定义是指:

a total of 18 digits

共18位数字

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

其中4位数位于小数点后面(因此在此之前为14位)

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

默认值 - 如果你没有指定别的 - 是十进制=十进制(18,0)

#2

Try changing your declarations as follows:

尝试更改声明,如下所示:

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

#1