在sql中找到分钟部分 [英] finding minutes part in sql

问题描述

我写了这段代码来找出TotalRequiredHours之间的区别 -

i wrote this code to find difference between TotalRequiredHours -

TotalHoursWorked =192 - 
TotalHoursWorked=186:04 

gives result = 5.933334, 

，它返回结果，小时是正确的但是分钟部分似乎是不正确的。例如为什么？如何找到精确的分钟部分，如他的格式5:56





代码：

, it returns result, Hours are right but minutes part seems to be incorrect. e.g. why ? how to find exact minutes part like int his format 5:56


CODE:

ALTER FUNCTION [dbo].[GetHoursDeducted]
(
	@emplID int,
	@month varchar(50)
)
RETURNS float
AS
BEGIN
	Declare @StartDate Date,  @EndDate Date, @mydate date, 
	        @TotalDays int, @TotalHours int, @BasicSalary float, 
			@BSalaryHour int, @TotalSalary float, @hms varchar(100),
			@hrs int, @mts int, @TotalHoursWorked float,
			@hrsDeducted float
	    
		set @hms = (Select OverallTime from MonthlyRecord where EmplID = @emplID AND Month = @month )
        Set @hrs = LEFT(@hms,charindex(':',@hms)-1)
	    set @mts=RIGHT(@hms,len(@hms)-charindex(':',@hms))
        set @TotalHoursWorked = @hrs + (@mts/60.0)
		Set @mydate = GETUTCDATE()
		Set @StartDate = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101))
		Set @EndDate = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101))
		Set @TotalDays =((DATEDIFF(dd, @StartDate, @EndDate) + 1)
		  -(DATEDIFF(wk, @StartDate, @EndDate) * 1)
		  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)) 
		Set @TotalHours  = (@TotalDays * 8)
		Set @BasicSalary = (Select BasicSalary from HrEmployee where EmplID=@emplID)
		--Set @TotalHoursWorked = (Select OverallTime from MonthlyRecord where EmplID = @emplID AND Month = @month )
		Set @BSalaryHour = @BasicSalary / @TotalHours
		Set @TotalSalary = @BSalaryHour * @TotalHoursWorked
		Set @hrsDeducted = @TotalHours - @TotalHoursWorked
		
		--------------------------------------------------------------------------------------
	
	-- Return the result of the function
	RETURN  @hrsDeducted

END

推荐答案

http://msdn.microsoft.com/en-us/library/ms174420.aspx [ ^ ]

我的逻辑是使用分钟进行计算，然后将结果转换为小时和分钟



ex：如果y是结果，那么时间可以得到y％60 +'：'+ y / 60


注意：代码未经过测试我刚刚给出了逻辑。

My logic would be to do calculation using minutes and then convert the result to hours and minutes

ex: if y is the result the time could be got as y%60+':'+y/60

NOTE: the code is not tested I have just given the logic.

这篇关于在sql中找到分钟部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！