Solved

Floor function in Microsoft SQL 2008

Posted on 2014-01-08
5
502 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

20 Experts available now in Live!

Get 1:1 Help Now