none
Help in Truncate the volume RRS feed

  • Question

  • Hello, How I can get the following output. I want to truncate decimal after five decimal. No rounding.

    Declare @d varchar(20)='100.224456'
    SELECT CAST(@d AS DECIMAL(12,5)) AS Volume

    Declare @d varchar(20)='100.3355667'
    SELECT CAST(@d AS DECIMAL(12,5)) AS Volume

    Output-1: 100.22445

    Output-2: 100.33556

    Thursday, August 24, 2017 10:49 AM

Answers

  • You can also do like below way


    Declare @d varchar(20)='100.224456'
    SELECT round(@d, 5, 1) AS Volume
    go

    Declare @d varchar(20)='100.3355667'
    SELECT round(@d, 5, 1) AS Volume

    • Proposed as answer by Bhadresh Vasani Thursday, August 24, 2017 12:53 PM
    • Marked as answer by Khan_K Thursday, August 24, 2017 3:17 PM
    Thursday, August 24, 2017 12:52 PM
  • Is the source data really a string? Then truncate the string 5 char after the dot

    Declare @d varchar(20)='100.224456' SELECT CAST(LEFT(@d, CHARINDEX('.', @d) + 5) AS DECIMAL(12,5)) AS Volume go

    Declare @d varchar(20)='100.3355667' SELECT CAST(LEFT(@d, CHARINDEX('.', @d) + 5) AS DECIMAL(12,5)) AS Volume



    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Russ Loski Thursday, August 24, 2017 11:22 AM
    • Marked as answer by Khan_K Thursday, August 24, 2017 11:32 AM
    Thursday, August 24, 2017 11:18 AM
  • Now if it is really a decimal(12,6) rather than string.

    Declare @d  DECIMAL(12,6)= 100.224456 
    SELECT CAST(@d AS DECIMAL(12,5)) AS Volume
    Declare @d2   DECIMAL(12,6)=100.3355667
    SELECT CAST(@d2 AS DECIMAL(12,5)) AS Volume
    
    select  cast(floor( @d   * 100000)/100000.0 AS DECIMAL(12,5))
    select  cast(floor( @d2   * 100000)/100000.0 AS DECIMAL(12,5))


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Khan_K Thursday, August 24, 2017 11:32 AM
    Thursday, August 24, 2017 11:26 AM
  • Hi Khan_k,

    If the source data is decimal data type, you may use ROUND function to achieve your needs.

    Declare @d decimal(12,8)=100.224456
    
    select cast(round(@d,5,1) as decimal(12,5)) as Volume
    
    Declare @d1 decimal(12,8)=100.3355667
    
    select cast(ROUND(@d1,5,1) as decimal(12,5)) as Volume 

    Reference:https://docs.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql

    Note: The content is under the heading "C. Using ROUND to truncate" section

    Best Regards,

    Will


    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.

    Thursday, August 24, 2017 11:34 AM
    Moderator

All replies

  • Is the source data really a string? Then truncate the string 5 char after the dot

    Declare @d varchar(20)='100.224456' SELECT CAST(LEFT(@d, CHARINDEX('.', @d) + 5) AS DECIMAL(12,5)) AS Volume go

    Declare @d varchar(20)='100.3355667' SELECT CAST(LEFT(@d, CHARINDEX('.', @d) + 5) AS DECIMAL(12,5)) AS Volume



    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Russ Loski Thursday, August 24, 2017 11:22 AM
    • Marked as answer by Khan_K Thursday, August 24, 2017 11:32 AM
    Thursday, August 24, 2017 11:18 AM
  • Now if it is really a decimal(12,6) rather than string.

    Declare @d  DECIMAL(12,6)= 100.224456 
    SELECT CAST(@d AS DECIMAL(12,5)) AS Volume
    Declare @d2   DECIMAL(12,6)=100.3355667
    SELECT CAST(@d2 AS DECIMAL(12,5)) AS Volume
    
    select  cast(floor( @d   * 100000)/100000.0 AS DECIMAL(12,5))
    select  cast(floor( @d2   * 100000)/100000.0 AS DECIMAL(12,5))


    Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by Khan_K Thursday, August 24, 2017 11:32 AM
    Thursday, August 24, 2017 11:26 AM
  • Thanks all
    Thursday, August 24, 2017 11:32 AM
  • Hi Khan_k,

    If the source data is decimal data type, you may use ROUND function to achieve your needs.

    Declare @d decimal(12,8)=100.224456
    
    select cast(round(@d,5,1) as decimal(12,5)) as Volume
    
    Declare @d1 decimal(12,8)=100.3355667
    
    select cast(ROUND(@d1,5,1) as decimal(12,5)) as Volume 

    Reference:https://docs.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql

    Note: The content is under the heading "C. Using ROUND to truncate" section

    Best Regards,

    Will


    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.

    Thursday, August 24, 2017 11:34 AM
    Moderator
  • You can also do like below way


    Declare @d varchar(20)='100.224456'
    SELECT round(@d, 5, 1) AS Volume
    go

    Declare @d varchar(20)='100.3355667'
    SELECT round(@d, 5, 1) AS Volume

    • Proposed as answer by Bhadresh Vasani Thursday, August 24, 2017 12:53 PM
    • Marked as answer by Khan_K Thursday, August 24, 2017 3:17 PM
    Thursday, August 24, 2017 12:52 PM
  • Very helpful.
    Monday, October 21, 2019 7:25 AM
  • this Post very helpful for me
    Monday, October 21, 2019 7:26 AM