Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: divide 4 /33 and mulitiply by -0.00344869766265843 does not give correct result

    declare @ValDateDays smallint,@OptDateDays smallint

    SELECT @ValDateDays =CAST((DATEDIFF(d,@monTradeDate ,@ValueDate )) AS INT),

    @OptDateDays =CAST( (DATEDIFF(d,@monTradeDate, @monOptionDate )) AS INT)

    --here @ValDateDays =4 and @OptDateDays =33

    the result (n3) should be =0.121212121212121 but displays as 0.000000000000000000

    SELECT @ValDateDays vn,@OptDateDays odays,

    CAST((@ValDateDays /@OptDateDays ) AS decimal(18,18)) as n3

    after i figure this out the result will be multiplied by

    @FwdPoints decimal(18,18) (-0.00344869766265843)

    and that will return -0.000418024.

    Thanks for the help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mikezx10 View Post
    CAST((@ValDateDays /@OptDateDays ) AS decimal(18,18))
    there's your problem

    it does the division first, which defaults to integer arithmetic, i.e. 0, then it casts 0 to decimal and comes up with 0.0

    try this instead --

    CAST(@ValDateDays AS DECIMAL(20,18)) / @OptDateDays

    note: 18,18 caused an overflow when i tested it, so i changed it to 20,18

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Posts
    233
    Thanks for the help !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •