the use of FORMAT

[CoffeeGuru]

I have a query that when run exports itself and opens up in Excel.

In the design query I set the format to Fixed,0 decimals or Fixed,2 decimals or Percent,2 decimals.

Eventually this has became a pass through query as my data is stored on SQL Server.

I'm not sure if its a result of the pass-through but when the query results open in Excel the formatting had gone and I was left with results up to 10 decimal places.
OK its easy for me to reformat the sheet but...

So I found that I can ammend my SQL code to read:
FORMAT(column_name, 'N0') will set zero decimals
FORMAT(column_name, 'N2') will set two decimals

Unfortunatley I now find that when it is exported to Excel the numbers are formated as text, yes it is to zero or 2 decimals, so I am now at a point where I need to force a numeric value as Excel needs to run calculations over thet output.

Ugh, whats going on?
example 1
FORMAT(tblProducts.[RRP W/O TAX],'N2') AS RRP,
RRP W/O TAX = float,null

example 2
FORMAT([CM_DATA].[stock]*[RRP W/O TAX],'N2') AS [Stock Value]
stock = float, null

 

[CJ_London]

The format function will convert to text, and formatting is not carried through to the export which is why you see more decimal places

Rather than using the format function for numbers, use the round function instead, or wrap the format function with the val function so

round(column_name, 0)
round(column_name, 2)
or
val(FORMAT(column_name, 'N0'))
val(FORMAT(column_name, 'N2'))

although the latter will be slower and can result in different values

e.g. using round to 2 dp will change 3.456 to 3.46 whilst format will result in 3.45

 

[CoffeeGuru]

Thanks CJ
I looked at Round before but as at the time I was looking for thousand seperators I ignored it.
I've gone back to it now.

Martin