CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Rounding Down number to the 60

How can I round a number to interval of 60 ? For instance if I have number 61, It should round of to 60 and if number is 150, it should round of to 120 and in case of 59 it should round off to zero.

Upvotes: 3

Views: 319

Answers (5)

Cato
Cato

Reputation: 3701

I'd prefer FLOOR over relying on Integer division, it's what FLOOR is for. example

DECLARE @YourVal DECIMAL(10,4) = 220.1234;
SELECT @YourVal / 60 * 60, FLOOR(@YourVal / 60) * 60;

Upvotes: 0

Tanner
Tanner

Reputation: 22743

Alternative is to use the modulus operator to get the remainder of a division by 60 then subtract it:

% (Modulus) (Transact-SQL)

Returns the remainder of one number divided by another.

For example:

declare @valueToTest int = 150
select @valueToTest - (@valueToTest % 60) as result

-- result: 120

So this gets the remainder when you divide the @valueToTest by 60 and subtracts it from the original @valueToTest.

Upvotes: 1

MarkD
MarkD

Reputation: 5316

Think this is what you're after

DECLARE @TestVal INT = 59
SELECT @TestVal - (@TestVal % 60)

SET @TestVal = 61
SELECT @TestVal - (@TestVal % 60)

SET @TestVal = 150
SELECT @TestVal - (@TestVal % 60)

Upvotes: 2

Paweł Dyl
Paweł Dyl

Reputation: 9143

To truncate values multiply by 60 and divide by 60. See demo:

SELECT 61/60*60 --result: 60
SELECT 59/60*60 --result: 0

Number must be int (bigint, smallint, tinyint). If it's not, use CAST/CONVERT.

See also Division (Transact SQL):

If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

Upvotes: 4

john McTighe
john McTighe

Reputation: 1181

ugly but it works: Integer Division Then Multiply

SELECT (125 / 60 ) * 60

Upvotes: 2

Related Questions