Truncate Decimal Values on Insertion

• Grinja

  Hall of Fame

  Points: 3590

  More actions

  June 2, 2011 at 8:19 am

  #389643

  Hey,

  Is it possible to prevent SQL server from rounding a value on Insertion?

  In the sample below: Inserting value 1000.9999 into a field type of less precision will round the values.

  MSDN describes the use of ROUND to truncate the decimals without rounding (Round_Truncate in sample).

  Is it possible to achieve this in any other way e.g in table definition or through hints?

  Thanks

  G

  CREATE TABLE #Rounder (

  Original DECIMAL (8,4)

  , Round_Implicit DECIMAL (8,2)

  , Round_Truncate DECIMAL(8,2))

  INSERT INTO #Rounder SELECT 1000.9999, 1000.9999, ROUND(1000.9999,2,1)

  SELECT *

  FROM #Rounder

  -- CLEANUP:

  DROP TABLE #Rounder

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  June 2, 2011 at 9:31 am

  #1333179

  Trigger?

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Vedran Kesegic

  SSCrazy Eights

  Points: 9302

  More actions

  June 2, 2011 at 10:08 am

  #1333212

  Maybe this ?

  SET NUMERIC_ROUNDABORT ON

  _____________________________________________________
  Microsoft Certified Master: SQL Server 2008
  XDetails Addin - for SQL Developers
  blog.sqlxdetails.com - Transaction log myths