CODEWITHSUNDEEP
sql-server
Stephen Patten
Stephen Patten

Reputation: 6363

How to truncate then round a number in SQL Server 2008 R2

Given these 2 examples, what is the correct syntax to round each example to the whole number. This must be a set operation as the decimal is from a column.

96.001 will be 96

80.01 will be 81

Thank you, Stephen

Upvotes: 1

Views: 2713

Answers (3)

Chains
Chains

Reputation: 13157

declare @myVar1 decimal(6,2)
declare @myVar2 decimal(6,2)
set @myvar1 = 96.001
set @myvar2 = 80.01

SELECT @myvar1, CAST(CEILING(@myVar1) as int), @myvar2, CEILING(@myVar2)

Result:

96.001   |   96   |   80.010   |   81

Upvotes: 0

Derek
Derek

Reputation: 23228

You are looking for the CEILING and FLOOR functions.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453287

;WITH T(C) AS
(
SELECT 96.001 
UNION ALL 
SELECT 80.01 
)

SELECT CEILING(CAST(C AS DECIMAL(18,2)))
FROM T

Upvotes: 4

Related Questions