Click here to Skip to main content
15,883,758 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
i wrote this code to find difference between TotalRequiredHours -

SQL
TotalHoursWorked =192 - 
TotalHoursWorked=186:04 

gives result = 5.933334, 


, 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:



SQL
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
Posted

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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900