How to format float values to remove exponential notation in SQL

[amelial]

I need help with formatting decimal numbers in my database table. The problem is that my float column keeps showing values in exponential format like 1.234567E+08 instead of regular numbers. I tried using different conversion methods but nothing seems to work properly.

Here’s what I attempted:

UPDATE MyTable 
SET TotalAmount = CONVERT(VARCHAR(15), TotalAmount)

But I still get the same exponential format in my results. I have a stored procedure that processes monthly data and calculates yearly totals. The issue happens when I try to display the final calculated values.

DECLARE @data_cursor CURSOR
DECLARE @current_amount FLOAT
DECLARE @item_name VARCHAR(50)
DECLARE @selected_year INT
DECLARE @start_date DATETIME
DECLARE @end_date DATETIME

SET @selected_year = 2023
SET @start_date = CAST(CAST(@selected_year AS VARCHAR(4)) + '0101' AS DATETIME)
SET @end_date = CAST(CAST(@selected_year AS VARCHAR(4)) + '1231' AS DATETIME)

SET @data_cursor = CURSOR FOR
SELECT ItemCode FROM Sales_Summary_Table

OPEN @data_cursor
FETCH NEXT FROM @data_cursor INTO @item_name

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT TOP 1 @current_amount = Amount
    FROM Transaction_History 
    WHERE ItemCode = @item_name
    AND RecordDate BETWEEN @start_date AND @end_date
    
    IF @current_amount IS NOT NULL
    BEGIN
        UPDATE Sales_Summary_Table
        SET Q1_Total = @current_amount
        WHERE CURRENT OF @data_cursor
    END
    
    FETCH NEXT FROM @data_cursor INTO @item_name
END

CLOSE @data_cursor
DEALLOCATE @data_cursor

UPDATE Sales_Summary_Table
SET Annual_Total = (Q1_Total + Q2_Total + Q3_Total + Q4_Total)

UPDATE Sales_Summary_Table  
SET Annual_Total = FORMAT(Annual_Total, 'N2')

The final totals still appear as scientific notation. What’s the correct way to convert these float values to display as normal decimal numbers?

[sophiac]

You’re mixing data types wrong. When you FORMAT() a float column and store it back as float, SQL Server converts that formatted string back to float - which brings back the exponential notation. I hit this exact issue last year with financial data. Either change your column to DECIMAL or VARCHAR, or only format during SELECT operations, not UPDATE. For your case, try: SELECT ItemCode, FORMAT(Annual_Total, ‘F2’) as FormattedTotal FROM Sales_Summary_Table. If you need the formatted values stored permanently, change Annual_Total to VARCHAR and your FORMAT() update will work. I’ve found DECIMAL(18,2) works way better than FLOAT for money calculations - no precision issues and no exponential notation headaches.

[Alex_Thunder]

Use CAST instead of FORMAT - CAST(Annual_Total as DECIMAL(15,2)) will fix the exponential display. Float’s messy for this stuff anyway, decimal’s way better for financial calculations.

[Neo_Movies]

You’re fighting SQL Server’s default float display behavior. Had the same issue with large inventory values showing up as 1.23E+07 in reports. STR() function worked way better than FORMAT or CONVERT for me. Try UPDATE Sales_Summary_Table SET Annual_Total = STR(Annual_Total, 15, 2) - just change your column to VARCHAR first. STR gives you actual control over output format and kills that exponential notation completely. Another option that saved me tons of headaches: create a computed column with formatting built in. Your base data stays numeric but displays right. Heads up - STR() pads with spaces on the left, so wrap it with LTRIM() if spacing matters for your output.