[Webinar] Streamline your web hosting managementRegister Today

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

Round column numbers to nearest dollar in Sql Server 2005

I need a method to Round a column's numbers to the nearest dollar in Sql Server 2005.

Any numbers with cents (123.20) the .20 breaks the table.

Thanks for your help!
0
dave_sky
Asked:
dave_sky
  • 3
  • 3
1 Solution
 
ThomasMcA2Commented:
In T-SQL, Round(123.20, 0) = 123.

This SQL guide is the only reference book that is on my desk. Whenever I have an SQL question, I look there first.
0
 
dave_skyAuthor Commented:
How would I structure that query?

The table is: tblPlumbingListBidDetail

The column name is: UnitPrice

I basically only work with the application that is attached to the database....
0
 
ThomasMcA2Commented:
Like this:

SELECT ROUND(UnitPrice, 0)
   FROM tblPlumbingListBidDetail

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dave_skyAuthor Commented:
It didn't work - probably because I have NULL values in the column?
0
 
ThomasMcA2Commented:
This version skips the null values:

SELECT ROUND(UnitPrice, 0)
   FROM tblPlumbingListBidDetail
WHERE UnitPrice IS NOT NULL

Open in new window


If you need other fields/columns besides UnitPrice, include them like this:

SELECT ROUND(UnitPrice, 0), Field1, Field2, Field3
   FROM tblPlumbingListBidDetail
WHERE UnitPrice IS NOT NULL

Open in new window

0
 
PortletPaulCommented:
You can use ISNULL or COALESCE to provide zero instead of null, like this:

SELECT ROUND(ISNULL(UnitPrice,0), 0), Field1, Field2, Field3
   FROM tblPlumbingListBidDetail

SELECT COALESCE(ISNULL(UnitPrice,0), 0), Field1, Field2, Field3
   FROM tblPlumbingListBidDetail

Open in new window

0
 
dave_skyAuthor Commented:
Thanks!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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