CODEWITHSUNDEEP
sqlsql-server-2008sql-convert
user3590485
user3590485

Reputation: 241

Error converting Sum[Field] as numeric or float

I am using a function to get total of a column field is :

ALTER FUNCTION [dbo].[GetTwoWeeklyWorkTime](@EmployeeID as int,@PayPeriodID as varchar(10))

  RETURNS int

  AS
  BEGIN
  DECLARE @StartDate DATETIME
  DECLARE @EndDate DATETIME

   Select @StartDate=[PeriodStartDate],@EndDate=[PeriodEndDate] from PayPeriod where PayPeriodId=@PayPeriodID
-- Declare the return variable here
DECLARE @ResultVar numeric(10,2)

SELECT @ResultVar=

  Sum([WorkingTime])/60

  FROM [DayLog] Where EmployeeId =@EmployeeID AND CreatedDate between @StartDate AND @endDate 

-- Return the result of the function
RETURN Isnull(@ResultVar,0)

END

At the line 

                 Sum([WorkingTime])/60

I get the result as int. How to convert or Cast it into numeric or float..?

EDIT

I tried flowing:

             Sum(Cast([WorkingTime] as float))/60

             Sum([WorkingTime])/60.0

But no success.

Upvotes: 1

Views: 621

Answers (3)

Birby
Birby

Reputation: 331

Change 

RETURNS int

to

RETURNS decimal (10,2)

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425371

INT / INT would result in an INT, even though result could be a fraction.

Use this:

Sum([WorkingTime]) / 60.0

Upvotes: 0

D Stanley
D Stanley

Reputation: 152556

Well, you can calculate it as a float using

Sum(Cast([WorkingTime] as float))/60

but you also need to change your function return value to float:

RETURNS float

Upvotes: 2

Related Questions