format number in a query output

• techzone12

  SSCommitted

  Points: 1798

  More actions

  December 1, 2011 at 10:48 am

  #263918

  Platform: (microsoft SQL 2005 server)

  I have a SQL query that outputs values in this format: "73.190002" . I would like to format the output so it looks like "73.1".

  select PointName, pointValue from DataTable

  Thanks

• techzone12

  SSCommitted

  Points: 1798

  More actions

  December 1, 2011 at 11:08 am

  #1415457

  CAST(PointValue AS dec(10,1))

  This one was simple.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  December 1, 2011 at 11:33 am

  #1415476

  Try this:

  DECLARE @D DECIMAL(18,6)

  SET @D = 73.190002

  SELECT CAST(CAST(ROUND(@D,1,2) AS VARCHAR(10)) AS DECIMAL(18,1)),CAST(@d AS dec(10,1)) AS 'OOPS'

  Results:

  (No column name)OOPS

  73.1 73.2

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Lamprey13

  SSCertifiable

  Points: 6210

  More actions

  December 1, 2011 at 12:16 pm

  #1415505

  Slightly shorter to use the Function (truncate) of the ROUND function:CAST(ROUND(@D,1, 1) AS DECIMAL (18,1))