converting int to decimal

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jjaustx - 31 Jul 2008 15:01 GMT

I have a column of int data type consisting of values such as 10001, 9657 and
2389 in a sql server database. How can I convert these to values of 100.01,
96.57 and 23.89 in Transact-sql? Thanks.

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 31 Jul 2008 15:07 GMT

SELECT CONVERT(DECIMAL(9,2), int_column / 100.0) FROM table;

On 7/31/08 10:01 AM, in article
F404FB4B-9EF7-4DB2-9A3F-EC9779C964DD@microsoft.com, "jjaustx"

> I have a column of int data type consisting of values such as 10001, 9657 and
> 2389 in a sql server database. How can I convert these to values of 100.01,
> 96.57 and 23.89 in Transact-sql? Thanks.

Reply to this Message

jjaustx - 31 Jul 2008 15:35 GMT

Thanks so much for your help. I was trying:

SELECT CONVERT(DECIMAL(9,2), int_column / 100) FROM table;

and it wasn't working.

> SELECT CONVERT(DECIMAL(9,2), int_column / 100.0) FROM table;
>
[quoted text clipped - 4 lines]
> > 2389 in a sql server database. How can I convert these to values of 100.01,
> > 96.57 and 23.89 in Transact-sql? Thanks.

Reply to this Message

Plamen Ratchev - 31 Jul 2008 15:10 GMT

Try this:

SELECT CAST(10001/100.0 AS DECIMAL(5, 2)),
CAST(9657/100.0 AS DECIMAL(5, 2)),
CAST(2389/100.0 AS DECIMAL(5, 2))

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

jjaustx - 31 Jul 2008 15:40 GMT

Thanks a lot! The trick is "10001/100.0" I was using "10001/100" and failed.

> Try this:
>
[quoted text clipped - 4 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Tom Moreau - 31 Jul 2008 15:13 GMT

Try:

select

cast (x / 100.0 as decimal (5, 2))

from

(

select 10001 union all

select 9657 union all

select 2389

) as z (x)

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

I have a column of int data type consisting of values such as 10001, 9657
and
2389 in a sql server database. How can I convert these to values of 100.01,
96.57 and 23.89 in Transact-sql? Thanks.

Reply to this Message

jjaustx - 31 Jul 2008 15:42 GMT

Thanks you so much! The trick is "x/100.0" I was using "x/100" and failed.

> Try:
>
[quoted text clipped - 18 lines]
> 2389 in a sql server database. How can I convert these to values of 100.01,
> 96.57 and 23.89 in Transact-sql? Thanks.

Reply to this Message

Tom Moreau - 31 Jul 2008 15:51 GMT

Yep. When you divide an int by an int, you get an int, which truncates the
remainder.

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

Thanks you so much! The trick is "x/100.0" I was using "x/100" and failed.

"Tom Moreau" wrote:

> Try:
>
[quoted text clipped - 19 lines]
> 100.01,
> 96.57 and 23.89 in Transact-sql? Thanks.

Reply to this Message

vinu - 31 Jul 2008 15:20 GMT

hi

declare @i int
set @i=10001
select cast( (@i/100.0) as decimal(5,2))

vinu
http://oneplace4sql.blogspot.com/

>I have a column of int data type consisting of values such as 10001, 9657
>and
> 2389 in a sql server database. How can I convert these to values of
> 100.01,
> 96.57 and 23.89 in Transact-sql? Thanks.

Reply to this Message