CODEWITHSUNDEEP
sql-server
saafiyah
saafiyah

Reputation: 19

How do I round off both positive and negative numbers using one query in T-SQL?

left(cast(round(s.test, 0) * 100, 3 as varchar(36)), 5) + '%'

This displays the results I want, but when it is a negative number like -0.005036 it does not display to 3 decimal places; instead, it displays -0.54.

Upvotes: 0

Views: 580

Answers (1)

Mark Sowul
Mark Sowul

Reputation: 10600

What version of SQL server? Are you sure you don't just want FORMAT(s.test, 'P3')? FORMAT should work with 2014 and later. That's probably the best way to do text formatting within SQL. More documentation here: https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql

For a full explanation of these formatting patterns, consult the .NET Framework documentation on string formatting in general, custom date and time formats, and custom number formats. A good starting point is the topic, "Formatting Types."

Upvotes: 1

Related Questions