Solved

Floor function in Microsoft SQL 2008

Posted on 2014-01-08
5
538 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…

730 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