Rounding Down number to the 60

user_9188324 0
Viewed 75

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.

sql-server
5 Answers

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.

user_65792950

ugly but it works: Integer Division Then Multiply

SELECT (125 / 60 ) * 60
user_53165020

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)
user_19391030

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.

user_574750

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;
user_64090000
Related