Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    9

    Unanswered: convert data type

    I have SQL like;
    Code:
    SELECT
    	convert(decimal,c.[Balance] + c.[PaymentDebt]),
    	convert(decimal,c.[Balance] + c.[PaymentDebt])/1000000,
    	(c.[Balance] + c.[PaymentDebt]),
    	(c.[Balance] + c.[PaymentDebt])/1000000
    FROM 
    	dbo.SubsAcount c
    WHERE 
    	convert(decimal,c.[Balance] + c.[PaymentDebt])/1000000<-18
    result is
    Code:
    -128872364	-128.87236400	-128872364	-128
    -248390458	-248.39045800	-248390458	-248
    -2426948223	-2426.94822300	-2426948223	-2426
    I want to know that, why second and 4th fields are not same?
    I convert the sum of two field then divide it by 1000000 on second field, but on 4th field I don't convert it. First and third fields are same as you see and did same operation on them but got different results.

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by catastrophe View Post
    I want to know that, why second and 4th fields are not same?
    I convert the sum of two field then divide it by 1000000 on second field, but on 4th field I don't convert it.
    Haven't you answered your own question? In one you divide a decimal by an integer, in the other you you divide an integer by an integer.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2010
    Posts
    9
    No comment .
    Thank you very much

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you unhappy with my answer? It was a serious response that you had your finger on the answer from the start.

    You do know that if you have an integer as both a nominator and denominator you end up with an integer as the result? And that if you have a decimal as either the nominator or denominator you end up with a decimal as the result? I foget the proper name for this, but the result will be the highest precision data type of the nominator and denominator.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In fact, here is a further example:
    Code:
    SELECT
    	(c.[Balance] + c.[PaymentDebt] * 1e0)/1000000
    FROM 
    	dbo.SubsAcount c
    The same "up casting" happens with multiplication. Your result is now a float.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2010
    Posts
    9
    That is ok with your answer. I realize important point with your help. I am new with T-SQL but I have to solve problems quickly so I wrote to here.
    I saw difference with statement shown below.
    select 255985/100
    select convert(decimal,255985)/100
    select 255985/100.0
    select convert(real,255985)/100
    select convert(float,255985)/100

    Again thank you very much.

Posting Permissions

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