# Thread: Mod function in SQL 2000

1. Registered User
Join Date
Feb 2004
Posts
52

## Unanswered: Mod function in SQL 2000

I need to use the mod function on a numeric field with 2digits decimal.
I was told that SQL server does not support mod on a decimal number.

Is it correct? If yes, is there any way around this?

2. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
What does MOD do?

3. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
Modulus, right?

4. Registered User
Join Date
Feb 2004
Posts
52
Originally posted by Brett Kaiser
What does MOD do?

I meant MODULO. (Provides the remainder of one number divided by another.)

Thanks

5. Registered User
Join Date
Dec 2002
Posts
1,245
modulo isn't designed to work with decimals regardless if it's SQL or any other language.

You can must CAST or CONVERT the decimal to INT.

regards,

hmscott

6. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
You can multiply both numbers by 100 and cast them as INT, like this:

select cast(4.32 * 100 as int) & cast(5.87 * 100 as int)

7. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
Or, you can have this function:

Code:
```if object_id('dbo.fn_Modulo42Decimals') is not null
drop function dbo.fn_Modulo42Decimals
go
create function dbo.fn_Modulo42Decimals (
@First   decimal(18, 2),
@Second  decimal(18, 2)              ) returns int
as begin
return (
cast(@First * 100 as int) & cast(@Second * 100 as int)
)
end
go
select dbo.fn_Modulo42Decimals(5995.32, 154.67)```
Not sure if it actually works right, always hated math

8. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322

Code:
```DECLARE @x decimal(15,2), @y decimal(15,2)
SELECT @x = 12345.67, @y = 2.15
SELECT FLOOR(@x/@y)-@x/@y```

9. Registered User
Join Date
Jul 2003
Location
San Antonio, TX
Posts
3,662
But this is my favorite (courtesy of Kaiser's Bar & Grill - franchise inquiries are welcome):

DECLARE @Weekend datetime
SELECT @Weekend =
CONVERT(datetime,
CONVERT(varchar(10),GetDate(),120) + ' 17:00:00')
SELECT
DATEDIFF(mi, GetDate(), @Weekend)/60.00 As Hours_till_Margarittaville

10. Registered User
Join Date
Dec 2002
Location
Antwerp, Belgium
Posts
227
and how about when I have a value of 48 digits long? I declared a float(50),
but when I perform a division it keeps displaying an error message about the maximum precision of a numeric being 38. How can I do this or work around?

11. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
38 digits is the limit for numeric calculations in SQL Server. I don't know of any way around that limit within SQL.

You've piqued my curiouisity though... Why on earth would you care ?!?! What the heck would you store with fifty digits of precision ?

-PatP

12. Registered User
Join Date
Dec 2002
Location
Antwerp, Belgium
Posts
227
well, it's a barcode consisting of an employee's company number, department, dates, .... It's in total 50 digits long, the first 48 containing data and the last 2 are check digits (modulo 97). I need to check if the data is correctly recognized by our scanning software and if not, present that record to the operator that has to manually correct it.

13. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Hmmm... The only thing that comes to my mind would be to treat the barcode as two NUMERIC(30,0) columns.

-PatP

14. Registered User
Join Date
Dec 2002
Location
Antwerp, Belgium
Posts
227
And how would you do that? I don't think I can just cust the value in 2 parts and perform some calculations on it?

15. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595