Post reply

T-SQL small Question

  • November 20, 2004 at 5:22 am

    #162817

    In SQL editor, what will be the result of this query

     

    Select 100/3

     

    Will it be

    A)  33.33

    B)  Error

    C)  33.00

    D)   34

     

     


    Sudhir Chawla

  • November 20, 2004 at 9:09 am

    #530949

    hey sudhir

    i think it would be jus 33.b cos of integer division

    am i right or wrong?

    regards

    Rajiv.

  • November 20, 2004 at 1:46 pm

    #530958

    Select 100/3 

               

    -----------

    33

    (1 row(s) affected)

    and yes, it's because both operands are interpreted as integer, thus the result is also an integer. But why don't you try this yourself in QA? Should be only a matter of seconds. Actually I guess much faster than waiting here for an answer.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 20, 2004 at 9:43 pm

    #530971

    Guys,

     

    I have already tried this, it will give 33.00, the reason is that if we use any value which is int compatible, SQL compiler will does an implicit conversion & then produces the output in int format only.

    But if you put 100.00/3, it will give 33.33333 as result. The reason is it will treat this as numeric & division by integer = numeric result. This time the compiler will does an implicit conversion to numeric & then give the correct result.

    My idea was to share this knowledge. Thank guys.

     

    sudhir


    Sudhir Chawla

  • November 22, 2004 at 1:15 am

    #530992

    Well, I'd say the 33 in the first example is also "correct". When you read up on data type precedences in BOL you'll find it there explained.

    Also, for the lazycoder you can use

    select 100/3. or select 100./3

    to get the same result. Just put a dot in and SQL Server won't treat it as integer anymore.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 22, 2004 at 3:36 am

    #530999

    Sudhir,

    I agree with Frank and disagree with you. The answer is NOT 33.00.  If both numbers are INT as you show them, the answer will be INT. INT does not allow for decimals. The result must be a whole number so you can't have the .00.  I also tried this in Query Analyzer.

    -SQLBill

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply