Decimal Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Decimal Value

Author

Topic

ofsouto
Starting Member

22 Posts

Posted - 2005-02-17 : 08:52:02

Hi, dear

When I execute the queries bellow I get an int value but I need a decimal value.

SELECT 10/3
-----------
3

SELECT CAST(10/3 AS DECIMAL(5,2))
-------
3.00

How can I get 3.333333333333333 or 3.33?
Thank you very much.

Obede
Brazil

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-17 : 09:00:17

SELECT 10/CAST(3 AS DECIMAL(14,12))

Go with the flow & have fun! Else fight the flow

ofsouto
Starting Member

22 Posts

Posted - 2005-02-17 : 09:17:57

OK! It works
Thank you very much.

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-17 : 09:47:21

Mladen,

I tried your suggestion with Pi (355/113)

SELECT 355/CAST(113 AS DECIMAL(14,12))

and got this error:

Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

Semper fi, Xerxes, USMC(Ret.)
-------------------------------------------------------------------------
Once a Marine Programmer Analyst ALWAYS a Marine Programmer Analyst

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-02-17 : 10:03:41

break it apart step by step. Try this:

SELECT CAST(113 AS DECIMAL(14,12))

What happens? Can you see why?

(by the way , I'm sure you know this, but PI is not 355/113 .... I'd phrase it differently -- "I tried your suggestion with an approximation of PI(355/13)" )

- Jeff

TimS
Posting Yak Master

198 Posts

Posted - 2005-02-17 : 10:19:30

Try SELECT 10/3.0

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-17 : 10:20:50

quote:
Originally posted by jsmith8858

break it apart step by step. Try this:

SELECT CAST(113 AS DECIMAL(14,12))

What happens? Can you see why?

(by the way , I'm sure you know this, but PI is not 355/113 .... I'd phrase it differently -- "I tried your suggestion with an approximation of PI(355/13)" )

- Jeff

Yep, Jeff, I know about Pi. I've always presumed that when one mentions Pi, they are referring to an approximation. When I typed Pi (355/113) I was insinuating that particular flavor!

(as opposed to 22/7 or even 104348/33215---which is stunningly more accurate!)

Have a great day in surgery

, Dr. Cross Join!

Semper fi, Xerxes, USMC(Ret.)
-------------------------------------------------------------------------
Once a Marine Programmer Analyst ALWAYS a Marine Programmer Analyst

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-17 : 10:42:44

why do you get an error? because of scale to precision ratio:
SELECT 355/CAST(113 AS DECIMAL(14,12)) -- doesn't work
SELECT 355/CAST(113 AS DECIMAL(16,12)) -- works
SELECT 355/CAST(113 AS DECIMAL(14,10)) -- works

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-17 : 10:59:06

HAPPY BIRTHDAY, MLADEN!!!

Thanks for the clarification

! (I already knew that

)

Semper fi, Xerxes, USMC(Ret.)
-------------------------------------------------------------------------
Once a Marine Programmer Analyst ALWAYS a Marine Programmer Analyst

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-17 : 11:13:24

thanx!

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources