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?
Thanks in advanceWhat does MOD do?|||Modulus, right?|||Originally posted by Brett Kaiser
What does MOD do?

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

Thanks|||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|||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)|||Or, you can have this function:

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 :D|||How about

DECLARE @.x decimal(15,2), @.y decimal(15,2)
SELECT @.x = 12345.67, @.y = 2.15
SELECT FLOOR(@.x/@.y)-@.x/@.y|||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|||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?|||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|||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.|||Hmmm... The only thing that comes to my mind would be to treat the barcode as two NUMERIC(30,0) columns.

-PatP|||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?|||Company number?
Departement number?

These are not really numbers, they are codes. If you don't add it, subtract it, or multiply it, then it is a string, not a number. Store your barcode as a 50 character string.|||they are already stored as varchar, but I have to perform a check on the recognized number, so: (first 48 characters) modulo 97 = (last 2 characters)|||Applause, please...

create Function BigStringModulo(@.BigNumString varchar(500), @.Divisor int)
returns int
as
--Function BigStringModulo
--blindman, 1/18/2005
--Returns the Modulo value of a large number expressed as a string value.
--Does not verify that the string is a valid number!

begin
declare @.WorkingString as varchar(500)
declare @.CalcString as varchar(8)
set @.WorkingString = @.BigNumString

set @.CalcString = left(@.WorkingString, 8)
while @.CalcString > @.Divisor
begin
set @.WorkingString = right(@.WorkingString, len(@.WorkingString) - len(@.CalcString))
set @.WorkingString = cast((@.CalcString % @.Divisor) as varchar) + @.WorkingString
set @.CalcString = left(@.WorkingString, 8)
end

return cast(@.CalcString as int)
end|||This may take some experimentation, but if I remember right

n * m mod p = ((n mod p) * (m mod p)) mod p

and

n + m mod p = ((n mod p) + (m mod p)) mod p

In that case, you can break your number into two pieces

(substing (number, 1, 25) * 10^25 + substring(number 26, 22)) mod 97. Break down the individual sections, and you have an ugly but workable solution.|||I'll applaud when I figure out how it was done, but it looks like it works. Don't you ever think inside the box?

Oh, and my 10^25 should probably be 10^22, or whatever exponent is correct for breaking the number into two halves.|||Found a bug. Have to handle the case where there are multiple consecutive zeros:

drop Function BigStringModulo
go
create Function BigStringModulo(@.BigNumString varchar(500), @.Divisor int)
returns int
as

--Function BigStringModulo
--blindman, 1/18/2005
--Returns the Modulo value of a large number expressed as a string value.
--Does not verify that the string is a valid number!

--TestVariables
-- declare @.BigNumString varchar(500)
-- declare @.Divisor int
-- set @.BigNumString = '97000000000001'
-- set @.Divisor = 2

begin
declare @.WorkingString as varchar(500)
declare @.CalcString as varchar(7)
set @.WorkingString = @.BigNumString

set @.CalcString = left(@.WorkingString, 7)
while (@.CalcString > @.Divisor) or len(@.WorkingString) > len(@.Divisor)
begin
set @.WorkingString = right(@.WorkingString, len(@.WorkingString) - len(@.CalcString))
set @.WorkingString = cast((@.CalcString % @.Divisor) as varchar) + @.WorkingString
set @.CalcString = left(@.WorkingString, 7)
end

--select cast(@.CalcString as int)
return cast(@.CalcString as int)
end|||Thanks for your replies. I will take a look at it see if it works tommorow, as I am far past my working hours, so I'll be off home now :D|||I had to change
"while @.CalcString >= @.divisor"

in case the divisor and working string were the same, but it looks like you found the same bug, and solved it a different way.

I think I understand what you have done, now. Your solution is similar to mine, but you are tagging on the upper string at the beginning of the lower string, and effectively getting the power of ten included that way. Nice job, Blindman.|||I liked the algorithm, but as you found out the code was not heavily tested. It is tough to verify the results when there are no other functions that will duplicate it! Please let me know if you see any more bugs in it.|||Well, it works ... Thanks blindman and MCrowley|||That was a fun challenge! Thanks for posting it.sql