Solved

Floor function in Microsoft SQL 2008

Posted on 2014-01-08
5
508 Views
Last Modified: 2014-01-08
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
Comment
Question by:eli411
  • 2
  • 2
5 Comments
 
LVL 8

Assisted Solution

by:5teveo
5teveo earned 100 total points
ID: 39766583
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
 
LVL 2

Author Comment

by:eli411
ID: 39766609
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
 
LVL 15

Accepted Solution

by:
ChloesDad earned 400 total points
ID: 39766650
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
 
LVL 8

Expert Comment

by:5teveo
ID: 39766709
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
 
LVL 2

Author Comment

by:eli411
ID: 39766846
thanks Steveo!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now