• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 617
  • Last Modified:

Floor function in Microsoft SQL 2008

I want to use the floor function my SQL syntax
for an example
I wanted to change the value from 95.222 to 95.125 using the Floor function.  I can do it using the Excel Floor function, but I want to do the same thing in Microsoft SQL Server too.

I tried using the similar syntax but get an error message "The floor function requires 1 argument(s)

using this syntax

select (Floor(ProductCost, 0.125)) from Product

and the syntax is okay if I change to
Select (Floor(ProductCost)) from Product

anyone ever work on that before??
0
eli411
Asked:
eli411
  • 2
  • 2
2 Solutions
 
5teveoCommented:
try round function

DECLARE @value numeric(10,10)
SET @value = .5432167890
SELECT ROUND(@value, 1)  -- 0.5000000000
SELECT ROUND(@value, 2)  -- 0.5400000000
SELECT ROUND(@value, 3)  -- 0.5430000000
SELECT ROUND(@value, 4)  -- 0.5432000000
SELECT ROUND(@value, 5)  -- 0.5432200000
SELECT ROUND(@value, 6)  -- 0.5432170000
SELECT ROUND(@value, 7)  -- 0.5432168000
SELECT ROUND(@value, 8)  -- 0.5432167900
SELECT ROUND(@value, 9)  -- 0.5432167890
SELECT ROUND(@value, 10) -- 0.5432167890
SELECT CEILING(@value)   -- 1
SELECT FLOOR(@value)     -- 0
0
 
eli411Author Commented:
Steveo!  I want to floor any of the value using 0.125 so
95.222 will be floored to 95.125
96.9696 will be 96.875
97.7172 will be 97.625
and I tried to use the round function before but not working so well
Thanks anyway.  Any other idea?
0
 
ChloesDadCommented:
Floor in SQL server only rounds down to the previous integer, that's why you get the error message.

Use Floor to get the largest eighth, then multiply the number by 8, then do the floor, then divide by 8 again.

e.g.

Select ( ( Floor(ProductCost * 8) / 8) as FlooredValue) from Product
0
 
5teveoCommented:
oops! timing is everything!!

Declare @vVar decimal(16,5)
Declare @vSign decimal(16,5)

Set @vVar = 1234.252
Set @vSign = .125

select floor(@vVar / @vSign) * @vSign
0
 
eli411Author Commented:
thanks Steveo!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now