controlling number of decimal places

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

General SQL Server Forums

New to SQL Server Programming

controlling number of decimal places

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2011-04-06 : 10:36:17

select CAST(normhrs AS decimal(4,2))/80 as TotalFTE
from paidtemp
where (DUTYSTA = 'E')

The above displays 1.000000.

How do I change it to just display 3 decimal places?
The maximum number I'll have to the left of the decimal point will be 4, in case that matters.
Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-06 : 10:46:14

There are a couple of options that come to mind:

1. Explicitly cast the result to what you want (DECIMAL(7,3)).
2. Use a fucntion like ROUND.
3. If this is a display issue, let the front end handle the formatting.

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2011-04-06 : 10:51:42

These suggestions aren't working. Now I have:
select round(CAST(normhrs AS decimal(7,3))/80, 3)
from paidtemp
where (DUTYSTA = 'E')

and I still get:
1.000000

How come neither (7,3) or Round does it?

I guess I could make it into a display issue, if I can't figure out how to do it in SQL.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-06 : 11:01:30

select CAST(normhrs/80 AS decimal(7,3))
from paidtemp
where (DUTYSTA = 'E')

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-06 : 11:21:26

Yeah, you need to cast the RESULT, as Robvolk showed.

Also, the ROUND function won't work. I keep forgetting ROUND returns the same data type as the value being rounded. Although it does "truncate", but not in a way that changes the datatype. It just controls rounding, for example:

SELECT 
    ROUND(CAST(99.456 AS DECIMAL(5,3)), 2, 1),
    ROUND(CAST(99.456 AS DECIMAL(5,3)), 2, 0)

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2011-04-06 : 11:31:45

quote:
Originally posted by robvolk

select CAST(normhrs/80 AS decimal(7,3))
from paidtemp
where (DUTYSTA = 'E')

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources