乘法值，四舍五入和CAST选择SQL

Question

我有一个SQL函数，它从多个记录中收集劳动时间，并使用STUFF(CAST AS VARCHAR(20))将它们连接到临时表中的单个列中。

我的问题是我的表中的劳动值以小时为单位并四舍五入到7位小数，我想将这些值转换为分钟并将其四舍五入到2位小数，然后将它们填充到我的临时表中。

Update:忘了说，我使用的是SQL Server 2008 R2

这是我的代码。

  @site nvarchar(20)
  ,@item nvarchar(30)
  ,@enviro nvarchar(30))
RETURNS nvarchar(MAX)
AS
BEGIN
    DECLARE @LbrHours AS nvarchar(MAX) = ''
    IF @enviro = 'Test'
    BEGIN
        IF @site = 'Arborg'
        BEGIN
            SET @LbrHours = STUFF((SELECT ',' + CAST(jrt.run_lbr_hrs AS VARCHAR(20))
                            FROM Arborg_Test_App.dbo.jobroute AS jbr
                                INNER JOIN Arborg_Test_App.dbo.job AS job
                                    ON job.job = jbr.job 
                                    AND job.suffix = jbr.suffix
                                INNER JOIN Arborg_Test_App.dbo.item AS itm
                                    ON itm.job = job.job
                                INNER JOIN Arborg_Test_App.dbo.jrt_sch AS jsh
                                    ON jbr.job = jsh.job 
                                    AND jbr.suffix = jsh.suffix 
                                    AND jbr.oper_num = jsh.oper_num
                                LEFT OUTER JOIN Arborg_Test_App.dbo.jrt_sch AS jrt
                                    ON jbr.job = jrt.job
                                    AND jbr.suffix = jrt.suffix
                                    AND jbr.oper_num = jrt.oper_num
                            WHERE  job.suffix = '0' and job.type = 'S' AND itm.item IS NOT NULL
                                AND itm.item = @item 
                                AND jbr.suffix = CASE -- Return Standard cost if Standard Operation exist, else return current cost
                                                    WHEN itm.cost_type = 'S'
                                                        THEN '1' -- '1' for standard operation
                                                    ELSE '0' -- '0' for current operations
                                                  END
                            ORDER BY itm.item, jbr.oper_num
                            FOR XML PATH('')), 1, 1, '')
            END 
        END
    RETURN @LbrHours
END

jrt.run_lbr_hrs是包含ERP表中以小时为单位的劳动时间的列。我如何将其乘以60，并在现有的STUFF(CASE AS NVARCHAR)中舍入到2位小数?

Answer 1

CAST为十进制，点后为2，然后为varchar

CAST(CAST(jrt.run_lbr_hrs * 60 AS DECIMAL(10, 2)) AS VARCHAR(20))

将小数维度更改为您需要的值

Answer 2

试试这个Str (jrt.run_lbr_hrs * 60, 2)