SQL Server Management Studio 2012 Rounding numbers without asking?

Michael 21 Reputation points
2021-01-19T22:11:37.013+00:00

I’m fairly new to SQL. My job uses SQL SMS 2012. We had an issue where our raw mainframe data was showing 0.0025 as the rate for an account but when I pulled the data with a SQL query it came out rounded to 0.0030. The original query without the cast section didn’t display enough decimal points. Also the rate field is not recognized as numeric. Is there a way I can get this to display 0.0025? Here is the query:

SELECT ACCT, cast(RATE as Decimal(10,4)) AS Rate FROM [RDatabase].[dbo].[ACCTDAILY] Where [ACCT] = ‘12345678’

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-01-20T09:19:31.46+00:00

    Hi @Michael

    Welcome to the Microsoft TSQL Q&A Forum!

    This is when the data is imported into SQL Server, it has been rounded.

    So to solve this problem, you need to check how the data is imported into SQL Server, and make some settings when importing the data.

    If the data is imported to SQL Server through SSIS, then when you encounter problems with import settings, you can add the SQL Server SSIS tag (sql -server-integration-services) when posting the problem. The people there will give you more professional advice.

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2021-01-19T22:34:08.067+00:00

    Don't blame SSMS. It only relays the data from SQL Server. Which gave you four decimals because you asked for it. You cast to decimal(10,4) which means that you have 10 digits of which four are decimals. So if you want to see five decimals...