Split integer valuefrom decimal valye

  • indraprakash

    Mr or Mrs. 500

    Points: 577

    Hi All,

    I want to split integer part from decimal value

    e.g. i have a no is 12456324.254 and i want to the output is 12456324

    Note: We can't use convert function

  • ralfik

    Valued Member

    Points: 65

    Function floor() does not work? :unsure:

  • indraprakash

    Mr or Mrs. 500

    Points: 577

    Thanks,

    It is working

  • PhilPacha

    Hall of Fame

    Points: 3525

    or, use

    ROUND(YourValue, 1)

    (revised) I'm sorry... I meant ROUND(YourValue, 0, 1)

    I apologize for my lack of proof-reading.

    The non-zero 2nd parameter truncates the value, without changing the datatype.

  • Jeff Moden

    SSC Guru

    Points: 993924

    indraprakash (7/7/2008)


    Note: We can't use convert function

    The ROUND function with the truncate option is definately the way to go... but why is it that you can't use the CONVERT function?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • indraprakash

    Mr or Mrs. 500

    Points: 577

    Hi,

    round function not gives proper output

    floor is right.

  • Jeff Moden

    SSC Guru

    Points: 993924

    indraprakash (7/8/2008)


    Hi,

    round function not gives proper output

    floor is right.

    Ummmm.... why do you think that? Using the optional 3rd parameter of ROUND makes it behave like FLOOR but with more control, if you need it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • PhilPacha

    Hall of Fame

    Points: 3525

    I'm sorry... I meant ROUND(YourValue, 0, 1)

    I apologize for my lack of proof-reading.

  • Jeff Moden

    SSC Guru

    Points: 993924

    No problem and thanks for the feedback, Phil... I've made similar mistakes... just wanted to be sure...:)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • vchandm23

    Old Hand

    Points: 304

    What if i want the other value not the floor value ...........

    i mean if its 1234.4321

    i want 4321 (values after the decimal place...)?

    Cheers,

    Chandra

  • vchandm23

    Old Hand

    Points: 304

    i know i can do the floor() on a decimal number and then store that to a variable and subtract it from the original number

    example

    @a = 2.75

    @B = floor(2.75)

    @C = @a - @B (this gives me 0.75)

    but is there a function that does this in SQL Server

    Cheers,

    Chandra

  • vchandm23

    Old Hand

    Points: 304

    i know i can do the floor() on a decimal number and then store that to a variable and subtract it from the original number

    example

    @a = 2.75

    @B = floor(2.75)

    @C = @a - @B (this gives me 0.75)

    but is there a function that does this in SQL Server

    Cheers,

    Chandra

  • Bruce W Cassidy

    SSChampion

    Points: 12481

    If you can't use convert(), you can always use cast(). BTW, I can't remember how floor() behaves for negative numbers, but I know there's a reason I end up using round() instead.

  • Jeff Moden

    SSC Guru

    Points: 993924

    vchandm23 (8/8/2011)


    but is there a function that does this in SQL Server

    Not in any version of SQL Server that I know of. You're pretty much stuck with doing the math like you did.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • krs

    SSC Journeyman

    Points: 91

    vchandm23:

    What about using modulus (%)? Like some flavor of the following:

    select replace(1234.4321 % 1 ,'0.','')

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply