T-SQL Question--right place?

Hot Topic

Multiple Attachments in Send Mail Task
Vista install help..
Connection error ( from MS Analysis to Applix TM1)
Handling errors in SSIS
a trigger code
saving xml to table
Problem with SQLBindParameter
Merge and Transactional from the same server
Oracle's TO_CHAR, what in SQL Server 2005?
Change SA password. Any effect to replication?

SQL Server

Report Layout - forcing absolute position
Package with WinZip hangs
on line idex rebuild
How to construct table with unique PK
restore from a differential backup
File Share Subscription
Custom Code for a Parameter
Transaction Management
mdx - filter using time range
Maintenance cleanup task not deleting files

T-SQL Question--right place?

Hello, all. I couldn't find a forum for T-SQL questions so I hope I am in the right place. I am creating a very simple UDF to calculate a percentage difference:

Create Function [dbo].[AmtDiffPercentPY](@CY decimal(15), @PY decimal(15))

Returns decimal(15)

as

begin

Declare @difference decimal

select @difference = (@CY-@PY)/@CY

return @difference

End

My problem is that when I use float, numeric, or decimal types I get an integer result of zero, when I use money I get a decimal result of 0.00, as if the division is using integer division and not returning the fractional value during division. Any advice

Thanks,

Lisa Morgan

Answer this question

T-SQL Question--right place?

  • sroughley

    Thank you! That was exactly what I needed to fix it up. I love Microsoft!

  • lemonsito

    Hi,

    unless you don’t specify a precision like decimal(16,2) which means sixteen digits and two decimal places, SQL Server assumes that you want 0 decimal places, which will treat the value as an integer.

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

  • A.Carter

    Did you try this, this didn't work....

    try this:

    declare @p decimal(10,2)

    select @p = 8/100

    select @p

    No decimal



  • T-SQL Question--right place?
Answer this question