How To Truncate Precision if = 0

[mickeyj2]

Hi,

I have a couple of fields in my table that are defined as DECIMAL(18,2). I am storing width/height size like 6.00 or 8.75. I need to keep these values as decimal, however, if the precision is .00 (e.g., 6.00), I'd like to truncate that in my SQL.

How can I do that in my SQL statement when I'm retrieving these values.

Thanks in advance for any help.

 

[LNBruno]

Sounds like a good case for a CASE statement....

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]

 

[SQLDenis]

select replace(val,'.00','') from table

example

create table #temp (val decimal(18,2))
insert #temp
select 13.45 as a
union all
select 6.00
union all
select 12.00
union all
select 45.66

select replace(val,'.00','') from #temp

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

What about values like 8.20? Should they be converted to 8.2?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mickeyj2]

Thanks for your replies.

Yes, I suppose that 8.20 could be truncated to 8.2. We are talking about size, so 6 in x 8.2 in would be sufficient.

Thanks for your help.

 

[gmmastros]

For what it's worth, I strongly encourage you to format the data in the 'front end' application that is showing it.

In my opinion, SQL Server is awesome at data storage and retrieval (the back end). Your front end (asp, vb, php, etc...) would be better suited to present the data to the user.

This means that you should NOT be concerned about data presentation from SQL Server. So what if the number has trailing zero's. The number is still the same. The data is still the same. If you want to 'pretty up' the data, do it in your front end.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mickeyj2]

Ok, I'll guess I'll format in the application. Thanks for the suggestion.