Professional Web Applications Themes

Converting or Casting FLOAT to INT - Microsoft SQL / MS SQL Server

Running this using SQL query yser : declare InvMonth int set InvMonth = 24043 declare Billyear int set Billyear = InvMonth / 12 declare Billmonth float set Billmonth = InvMonth set Billmonth= (((Billmonth/12)-Billyear)*12)+1 print Billmonth print cast(Billmonth as int) produces this result 24043 8 7 Can someone please explain why conversion to a integer seems to produce a result consistently one digit less than it should be? Regards, IGL...

  1. #1

    Default Converting or Casting FLOAT to INT

    Running this using SQL query yser :
    declare InvMonth int
    set InvMonth = 24043
    declare Billyear int
    set Billyear = InvMonth / 12
    declare Billmonth float
    set Billmonth = InvMonth
    set Billmonth= (((Billmonth/12)-Billyear)*12)+1
    print Billmonth
    print cast(Billmonth as int)

    produces this result
    24043
    8
    7

    Can someone please explain why conversion to a integer seems to
    produce a result consistently one digit less than it should be?

    Regards,

    IGL
    Ian Guest

  2. #2

    Default Re: Converting or Casting FLOAT to INT

    float is not an accurate datatype you will not get accurate results if you
    use FLOAT as a datatype use DECIMAL instead.
    ex;
    declare InvMonth int
    set InvMonth = 24043
    declare Billyear int
    set Billyear = InvMonth / 12
    declare Billmonth decimal(10,2)
    set Billmonth = InvMonth
    set Billmonth= (((Billmonth/12)-Billyear)*12)+1
    print Billmonth
    print cast(Billmonth as int)

    --
    -Vishal

    "Ian Lowson" <com> wrote in message
    news:google.com... 


    Vishal Guest

  3. #3

    Default Re: Converting or Casting FLOAT to INT

    Ian,

    The fraction 24043/12 cannot be represented exactly
    as a float, and it turns out that the nearest representable
    floating point number is just a hair less than the true value
    of the fraction. As a result, Billmonth ends up with
    a value of roughly 7.9999999999990905 after your
    calculation, which casts to 7, since casting to int truncates.

    If the problem you are trying to solve is really about
    integers, you might try to find a solution that uses integers
    only:

    declare InvMonth int
    set InvMonth = 24043
    declare Billyear int
    set Billyear = InvMonth / 12
    select Billyear
    declare Billmonth int
    set Billmonth = InvMonth % 12 + 1
    select Billmonth

    For the record, despite the fact that decimal types
    are billed as "exact", they are only exact insofar as they
    are able to accurately represent typed values in base 10.
    Even a decimal value cannot represent the fraction
    24043/12 exactly.

    -- Steve Kass
    -- Drew University
    -- Ref: A95D5E43-AA93-416E-9B85-5828FD4E5165


    Ian Lowson wrote:
     

    Steve Guest

Similar Threads

  1. #39680 [NEW]: Type Casting from float into int
    By diefans at googlemail dot com in forum PHP Bugs
    Replies: 4
    Last Post: November 29th, 01:40 PM
  2. CASTING
    By Anton in forum Informix
    Replies: 4
    Last Post: October 24th, 04:27 PM
  3. #25562 [Opn->Bgs]: Float to String to Float conversion error
    By helly@php.net in forum PHP Development
    Replies: 0
    Last Post: September 16th, 08:02 PM
  4. #25562 [NEW]: Float to String to Float conversion error
    By daseymour at 3hc dot org in forum PHP Development
    Replies: 0
    Last Post: September 16th, 07:36 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139