Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Floor function in Microsoft SQL 2008

Posted on 2014-01-08
5
Medium Priority
?
592 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 400 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 1600 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

636 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