# Help in Truncate the volume • ### 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

• 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 Thursday, August 24, 2017 12:53 PM
• Marked as answer by 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 Thursday, August 24, 2017 11:22 AM
• Marked as answer by 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))```

• Marked as answer by 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 ```

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

### 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 Thursday, August 24, 2017 11:22 AM
• Marked as answer by 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))```

• Marked as answer by 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 ```

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
• 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 Thursday, August 24, 2017 12:53 PM
• Marked as answer by Thursday, August 24, 2017 3:17 PM
Thursday, August 24, 2017 12:52 PM
• • 