将时间乘以整数 [英] multiplying time with whole number

问题描述

  FUNCTION  [dbo]。[GetTotalSalary1_func] 
（
  @emplID   int ，
  @ month   VARCHAR （ 50 ）=  NULL  
）
  RETURNS   int  
  AS  
  BEGIN  
 声明  @ BSalaryHour   int ， @TotalSalary   int ， @TotalHoursWorked   int  
  Set   @TotalHoursWorked  =（选择 OverallTime 来自 MonthlyRecord 其中 EmplID =  @ emplID   AND 月=  @ month ）
 设置  @TotalSalary  =  @ BSalaryHour  *  @ TotalHoursWorked  
   -    --------------- -------------------------------------------------- -------------------  
 
   -   返回函数的结果 
  RETURN   @TotalSalary  
 
  END  
 
 选择 dbo。 GetTotalSalary1_func（ 5 ，'  2013-dec'）

它抛出这行错误：

 设置  @TotalSalary  =  @ BSalaryHour  *  @TotalHoursWorked  是这样的192：22或105：01我猜bcz它不能相乘：22，：01部分与@BsalaryHour。
 
 
 
怎么做？
 
 
 
注：
 
 
 
总体时间基本上是varchar（13）
解决方案
 
最好的方法是不将数值存储为字符串：将其记录为以小时为单位的浮点值（因此6小时15分钟为6.25或者作为一些工作分钟。
 
 
 
每次在基于字符串的列中存储数字，日期或任何其他可处理的值时，您最终会给你的当你试图使用它们时会出现大量问题。这是可能的 - 你必须解析输入并允许各种错误 - 但它很复杂，而且很容易确保数据库首先包含可用的值。
 
 
FUNCTION [dbo].[GetTotalSalary1_func] 
(
	@emplID int,
	@month VARCHAR(50) = NULL
)
RETURNS int
AS
BEGIN
	Declare @BSalaryHour int, @TotalSalary int, @TotalHoursWorked int
		     Set @TotalHoursWorked = (Select OverallTime from MonthlyRecord where EmplID = @emplID AND Month = @month )
		Set @TotalSalary = @BSalaryHour * @TotalHoursWorked
		--------------------------------------------------------------------------------------
	
	-- Return the result of the function
	RETURN @TotalSalary

END

select dbo.GetTotalSalary1_func(5,'2013-dec')


it throws error on this line:



Set @TotalSalary = @BSalaryHour * @TotalHoursWorked


when @totalHoursWorked returned are like this 192:22 or 105:01 i guess bcz it can't multiply :22, :01 part with @BsalaryHour.



so how to do it ?



Note: 



OverallTime is basically varchar(13)
解决方案
Best way is not to store numeric values as strings: Either record it as a floating point value in hours (so 6 hours 15 minutes is 6.25) or as a number of minutes worked.



Every time you store numbers, dates, or any other "processable" value in string based columns, you end up giving yourself massive problems when you try to use them. It's possible - you would have to parse the input and allow for all sorts of errors - but it's complex, and a lot, lot easier to just ensure your database contains usable values in the first place.


                        
这篇关于将时间乘以整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！