Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Feb 2004
    Posts
    52

    Question 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?

    Thanks in advance

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What does MOD do?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

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

  4. #4
    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. #5
    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. #6
    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. #7
    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. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about

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

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    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. #10
    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?
    Johan

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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. #12
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227

    Cool

    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.
    Johan

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

    -PatP

  14. #14
    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?
    Johan

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •