Saving percent with two decimal places in SQL Server

Posted on 2007-10-02
Last Modified: 2008-01-09

So, here's a relatively easy one I think:

In a table designed like: tblProject (ProjectName, PercentOverBudget)

I want the PercentOverBudget field to be a percentage with two decimal places max and 4 places to the left of the decimal (so a valid field could be 1115.53).

I have two questions:

1. How should I declare the PercentOverBudget in SQL Server 2005?
2. I am going to write a stored procedure from an web application that is going to fill this table. How will I refer to the type of data in ASP.Net?

Question by:freezegravity
    LVL 18

    Accepted Solution

    Try this:

    create table #Y (col1 decimal(6,2))

    insert into #Y values(100)
    insert into #Y values(10.1234)
    insert into #Y values(0)
    insert into #Y values(12345.6789)

    select * from #Y

    -->> Output:

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)
    Msg 8115, Level 16, State 8, Line 6
    Arithmetic overflow error converting numeric to data type numeric.
    The statement has been terminated.
    -- >> 12345 is to big ! Error is expected !

    -->> Result:


    So the data type you are looking for is decimal(6,2)

    Hope this helps ...
    LVL 18

    Expert Comment

    Glad I could help and thanks for the grade !

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Syntax question II 2 26
    Add json string to a javascript object 9 34
    Async and await with MVC and Web Api 3 22
    varchar (max) 5 22
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now