asked on

SQL Not displaying numbers properly

I am having an issue with SQL not returning a value properly. I am using the following:
(Test_Value_One * 100) AS Test_Value_Goal
the value of Test_Value_One is .075. I am writing this to an xml file but the result displayed is: 7.500000000000000e+001
Not sure why this is occurring. Any assistance would be greatly appreciated.

slightwv (䄆 Netminder)

Try manually rounding it to the positions you want.

What database product?
How are you generating the XML?

Michelle Jackson

ASKER

Thanks for your reply. here is the code snippet in its' entirety

--execute master..XP_CMDSHELL 'SQLCMD -S sqlnetwork -Q "Select * from test.dbo.Test_Data" -s "," -o "Z:\DailyKPI.csv"';
DECLARE @xml2 AS XML = (SELECT LOCNCODE, ACT1_Office, '$' + FORMAT(This_Value1, 'N0') AS 
New_Value, Second_Value, Third_Value, ROUND((Test_Time * 100), 0) AS Test_Time, 
(Test_Time_Goal * 100) AS Test_Time_Goal,Fourth_Value, Fifth_Value
FROM [xxx].[dbo].[Test__Data] where LOCNCODE = 'CORP' FOR XML PATH('item'), ROOT('main'))
DECLARE @xmlChar2 AS VARCHAR(max) = CAST(@xml2 AS VARCHAR(max))
SET @xmlChar2 = REPLACE(REPLACE(@xmlChar2, '>', '^>'), '<', '^<')
DECLARE @command2 VARCHAR(8000) = 'echo ' + @xmlChar2 + ' > Y:\Offices\CORPORATE\TestDataI.xml'
EXEC xp_cmdshell @command2;

Open in new window

slightwv (䄆 Netminder)

OK, and?

I see you round test_time. Try rounding test_time_goal.

Michelle Jackson

ASKER

test_time is displaying incorrectly as well when I round it it shows as 0.000000000000000e+000 (the actual value for test_time is currently 0

ASKER CERTIFIED SOLUTION

Michelle Jackson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave Baldwin

The 'STR' function might give you the display you want.
https://docs.microsoft.com/en-us/sql/t-sql/functions/str-transact-sql?view=sql-server-2017