locked
How to generate random decimal no by tsql RRS feed

  • Question

  • I want to generate random decimal no from .1 to .9. So tell how could i do it by tsql? Thanks
    Sunday, February 12, 2017 7:08 PM

Answers

  • Below is one method:

    SELECT CAST((ABS(CHECKSUM(NEWID()))%9+1)/10.0 AS decimal(1,1));


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Naomi N Monday, February 13, 2017 2:26 AM
    • Marked as answer by Mou_kolkata Monday, February 13, 2017 8:55 AM
    Sunday, February 12, 2017 7:18 PM
  • Hi Sir

    Please refer to the following example:

    select cast( rand()*10 as decimal)*0.1 as RANDOM

    Maybe it can help you. If you have any questions , please feel free to contact me. 

    Best Regards

    Vake Sun





    • Proposed as answer by Vake Sun Monday, February 13, 2017 2:53 AM
    • Marked as answer by Mou_kolkata Monday, February 13, 2017 8:56 AM
    • Edited by Vake Sun Thursday, February 16, 2017 3:56 AM
    Monday, February 13, 2017 1:59 AM
  • Hi Mou_Kolkata,

    As for RAND (Transact-SQL), please see expression below.

    SELECT CEILING(RAND() * 9) * 0.1

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Vake Sun Monday, February 13, 2017 5:50 AM
    • Marked as answer by Mou_kolkata Monday, February 13, 2017 8:56 AM
    Monday, February 13, 2017 5:30 AM

All replies

  • Below is one method:

    SELECT CAST((ABS(CHECKSUM(NEWID()))%9+1)/10.0 AS decimal(1,1));


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Naomi N Monday, February 13, 2017 2:26 AM
    • Marked as answer by Mou_kolkata Monday, February 13, 2017 8:55 AM
    Sunday, February 12, 2017 7:18 PM
  • Hi Sir

    Please refer to the following example:

    select cast( rand()*10 as decimal)*0.1 as RANDOM

    Maybe it can help you. If you have any questions , please feel free to contact me. 

    Best Regards

    Vake Sun





    • Proposed as answer by Vake Sun Monday, February 13, 2017 2:53 AM
    • Marked as answer by Mou_kolkata Monday, February 13, 2017 8:56 AM
    • Edited by Vake Sun Thursday, February 16, 2017 3:56 AM
    Monday, February 13, 2017 1:59 AM
  • Hi Mou_Kolkata,

    As for RAND (Transact-SQL), please see expression below.

    SELECT CEILING(RAND() * 9) * 0.1

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Vake Sun Monday, February 13, 2017 5:50 AM
    • Marked as answer by Mou_kolkata Monday, February 13, 2017 8:56 AM
    Monday, February 13, 2017 5:30 AM
  • sweet and simple @Sam. thanks a lot :)
    Monday, February 13, 2017 8:56 AM