CODEWITHSUNDEEP
sqlexceldecimal
John Sinclair
John Sinclair

Reputation: 21

Limiting SQL Results to 2 Decimal Places in Excel

I have this very simple SQL query running in Excel:

SELECT "DCNR 595 C 24H|Well Based|Desc Run Depth|MWD_STEM|EM Temperature".Temperature
FROM ADI."DCNR 595 C 24H|Well Based|Desc Run Depth|MWD_STEM|EM Temperature" "DCNR 595 C 24H|Well Based|Desc Run Depth|MWD_STEM|EM Temperature"

And I would like to limit the output to two decimal places, rounding up as appropriate, instead of just truncating the result. So I have a series of results like this: 147.20 instead of 147.19999694824 in the Excel report.

I have tried CAST and ROUND functions, without success, but I am also struggling doing this inside of Excel. The MS Query application doesn't seem to like anything I do. :(

(Note: I cannot just format the Excel cells to show only 2 decimal places. The actual returned values have to be 2 decimal places.)

Upvotes: 2

Views: 4266

Answers (2)

Aaron Anodide
Aaron Anodide

Reputation: 17186

I just tried the ROUND function in Excel and it seems to work how you want. Personally, I'd favor this solution over a tweak to the source Query - it's simpler to manage in the long run because it's on the surface of the Excel document, not down in the Query definition.

enter image description here

Upvotes: 0

Russell Fox
Russell Fox

Reputation: 5435

try casting that field to decimal:

SELECT CAST(Temperature AS DECIMAL(18,2))

Upvotes: 0

Related Questions