May 11, 2011 at 10:08 am
Hi all:
I am having trouble with some decimal division inside of UDF.
For example, I want to get decimal 0.2 from following query:
Select CONVERT(decimal,SUBSTRING(CONVERT(varchar, '12:00:00'),1,2))/60
It returns 0.20000 as expected, however, my following UDF doesn't return decimal as I wanted
Create Function [dbo].TimeDiff
(
@timeIn Time,
@timeOut Time
)
Returns decimal
as
Begin
Declare @timeDiff decimal;
Declare @hourIn decimal;
Declare @minIn decimal;
Declare @hourOut decimal;
Declare @minOut decimal;
--Parse the string out of Time, get Hour and Min, then add them together as deciaml
Set @hourIn = CONVERT(decimal,SUBSTRING(CONVERT(varchar, @timeIn),1,2));
Set @minIn = CONVERT(decimal,SUBSTRING(CONVERT(varchar, @timeIn),4,2));
Set @hourOut = CONVERT(decimal,SUBSTRING(CONVERT(varchar, @timeOut),1,2));
Set @minOut = CONVERT(decimal,SUBSTRING(CONVERT(varchar, @timeOut),4,2));
Set @timeDiff = (@hourOut + @minOut/60.00) - (@hourIn + @minIn/60.00)
--This is the test I put for debugging, it returns integer 0
Declare @test-2 decimal
Set @test-2 = @minOut/60.00;
Return @timeDiff
End
Please help me, thanks
May 11, 2011 at 10:11 am
Specify the precision and scale when you declare your decimals. I don't offhand know the defaults, but it may not be what you intended.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2011 at 11:01 am
Default is (18,0) if that helps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply