[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

How can I format a number returned by a sql query

I am using MS-SQL 2005
I have a query that returns inventory items and quantities but the  numbers that are returned have five zeros at the end of each number

so 45 is returned as 45.00000

How can I get it to return just the 45?

Here is my query

Select
[Item #],
Description,
Altitm1,
Mfg,
[Item Class],
QTY_on_Hand,
QTY_Allocated
from vCustomerInventory

Thank You.
0
Wildone63
Asked:
Wildone63
1 Solution
 
chapmandewCommented:
cast it as an integer:

Select
[Item #],
Description,
Altitm1,
Mfg,
[Item Class],
cast(QTY_on_Hand as int) as qty_on_hand,
cast(QTY_Allocated as int) as qty_allocated
from vCustomerInventory
0
 
sqlrocksCommented:
You may want to consider ROUNDing or FLOORing the fields where you don't want as much precision.

Generic example:

declare @x numeric(18,5)

set @x = 45.5430

select @x
select floor(@x)
select cast(ROUND(@x, 0) as int)


Your query:

Select
[Item #],
Description,
Altitm1,
Mfg,
[Item Class],
FLOOR(QTY_on_Hand),
FLOOR(QTY_Allocated)
from vCustomerInventory

0
 
Wildone63Author Commented:
Thank You.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now