Round decimal

Question

Round decimal

GigiMi

SSC-Addicted

Points: 402
More actions
February 21, 2017 at 7:08 am

#327549

Hello,
when in my select I have 0,00 (decimal (4,2)), how can I round it to 0?
Thanks a lot.
Luigi

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

0.00 does equal 0, rounding it is still going to give you the same result (0.00). What is it you are after here exactly, only the Integer part of the number? What happens if you have the value 0.5, do you want to display 1 or 0?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

GigiMi SSC-Addicted Points: 402 More actions · Answer 2

For example, if I have the value 1,23 I still have 1,23, but if I get 0,00 I should show 0.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

GigiMi - Tuesday, February 21, 2017 7:17 AM
For example, if I have the value 1,23 I still have 1,23, but if I get 0,00 I should show 0.

This is a presentation issue, not a SQL issue. What application are you using to 'show' the various values?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Please don't do this. If your 0 is accurate to two decimal places, you should show 0.00 instead of 0. Otherwise, how do you know it's not 0.04 or 0.3 rounded down? If you must do it like this, have your presentation layer do it for you.

John

GigiMi SSC-Addicted Points: 402 More actions · Answer 5

Is a script task in a SSIS package, where from a datatable I create an output csv file.
And the user does not want to see 0,00 in this file, but simply 0.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Then use your favourite scripting language to search the CSV file for "0,00" and replace it with "0". Better still, explain to the user that he's wrong to want his file like that.

John

Thom A SSC Guru Points: 99130 More actions · Answer 7

As the others have said, this is presentation layer. If you were to reduce the accuracy of a column that contained 0's, you would have to apply that logic to the entire column. Thus, say you were only returning the integer part, 0.99 would become 0, 100.2 would become 100.

If, for example, you're using Excel and you want to separate Thousands with a comma and display just 0 for 0.00, you would use the format:
#,##0.00;-#,##0.00;0
This would give you the following values:
SQL Excel 1.00 1.00 1.20 1.20 1765.15 1,765.15 0.00 0 -6.59 -6.59 -6789.00 -6,789.00
Does that help?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99130 More actions · Answer 8

GigiMi - Tuesday, February 21, 2017 7:26 AM
Is a script task in a SSIS package, where from a datatable I create an output csv file.
And the user does not want to see 0,00 in this file, but simply 0.

Personally, I'd tell him to change the formatting on screen. The data should be as accurate as the data it represents. And, in your situation, that means 2 decimal places. The format version i gave above preserves that, it simply changes the displayed value, not the stored.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

GigiMi SSC-Addicted Points: 402 More actions · Answer 9

Ok, I'll try to correct this problem at the end (Excel), and see what they think.

Thanks a lot both.

Luigi