SQL 2012 :: Convert Division Of Int Result Into Decimal
Jul 4, 2015
Which one is good method to convert a following division into 2 decimal digits.
q1)
select cast(( cast( sum(population) as decimal) * 100) / sum(totalpopulation) as decimal(7,2)
from
table1 group by state
q2)
select cast(( sum(population) * cast( 100 as decimal)) / sum(totalpopulation) as decimal(7,2)
from
table1 group by state
q3)
select cast( (sum(population) * 100) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state
q4)
select cast(( cast(sum(population) as decimal) * 100) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state
q5)
select cast( (cast(sum(population) as decimal) * cast(100 as decimal) ) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state
q6)
select cast(( cast(sum(population) * 100 as decimal) ) / cast( sum(totalpopulation) as decimal) as decimal(7,2)
from
table1 group by state
I'm trying to insert into a table from an XML file. The mapping works OK however there is a problem with one of the fields. It is field name "Length" set up as Decimal(18,2) and it stops on the first row with an error, something like "Cannot convert to decimal". The values are all integers, such as "9", etc. but I presumed SQL would convert to "9.00" for example. It has worked for another field name "Weight", where values are stored in the XML file such as "0.28", etc. Does it reject it because it's an integer and needs to be to two decimal format in the XML?
I would expect the first one to give me the most accurate result but the second query with a lower precision and scale returns a result with a greater scale.Thanx.
Hi - I have what seems like a simple problem and I cannot find an answer to it. I'm hoping someone here can help.
I want to divide 2 numbers but have the answer come back with no decimal places. I don't want to round up or down..just give me the whole number and drop the decimal places. For example:
Select units/60 as answer
And units = 280. I want the "answer" to be 4 and NOT 4.66.
Hello Sir I am working on a Teacher Evaluation Project. In my database I store results from 1-5 as evaluation indicators. I apply AVG() function on the result column. The result of the query is in integer values (i.e) 4, 3 2 or 5. I want the resutl up to two decimal places. How can i write the query to get the result in decimal form?
I have a money field in SQL that when i try and get the sum of it i cannot convert it to decimal. This was working now its not, and nothing was changed.Any reason for the error? DECLARE @TEST decimal(10,2)SET @Test = (SELECT SUM(INV_Net) FROM abc.dbo.iSplit_Details WHERE LoanID='0000010604')Print @TestRETURNS: 160471.24----------------------------------------------------------------------------------------------------------------------------------------------------------------------------Specified cast is not valid. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Specified cast is not valid.Source Error:
Line 3576: // CURRENT TOTALLine 3577: cmd.CommandText = "SELECT SUM(INV_Net) FROM abc.dbo.iSplit_Details WHERE LoanID=@LoanID";Line 3578: decimal split_currentamt = ((decimal)cmd.ExecuteScalar());
I am trying to have an Excecute SQL Task return a single row result set executed on SQL Server 2005.
The query in the Execute SQL Task is: select 735.234, 2454.123
I get a conversion error when trying to assign to SSIS variables of type Double. I have nothing configured in the "Parameter Mapping" tab. I have the two SSIS Double variables mapped to the Tesult Name 0 and 1 in the "Result Set" tab
I don't want to use a for loop enumerator since there is a single row returned.
I simply want to assign these two values to SSIS Double variables (double is the closest match)
I can't even hack this by converting the decimals as string and then using DirectCast to convert them to Double.
Hi all,I have a column LateHours (Varchar). I want to put it in another table which has Latehours column in Decimal(4,2) Example = +04:33 Should be 4.33 (Only + values) Char to Decimal (4,2) Please Help me,Thanks,Janaka
Hello, is there a way to convert the value to just 2 decimal places, I created the report in Reporting Services and it has quite a few digits to each value. I looked at the table and found that the data type is {Float}. Is there a way to convert the values to just 2 decimal places?..Thank You.
Dim subtot As Double Dim tax As Double Dim tot As Double subtot = "0.00" Dim sql As String sql = "SELECT items.qty, products.descrip, products.price FROM items INNER JOIN products ON items.productid = products.id WHERE (items.orderid = " & Request.QueryString("oid") & ")"Dim objConn As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|AllStar.mdf;Integrated Security=True;User Instance=True") Dim cmdCustomers As New SqlCommand(sql, objConn)Dim dataReader As SqlDataReader objConn.Open() dataReader = cmdCustomers.ExecuteReader(CommandBehavior.CloseConnection)While dataReader.Read subtot = subtot + (dataReader.GetValue(0) * dataReader.GetValue(2)) End While tax = (subtot * 0.07) tot = (subtot + tax) Label1.Text = subtot Label2.Text = tax Label3.Text = tot ---------------------------------------- How to a convert the variable tax to just two decimals? I tried label2.text = CType(tax, Double) but that didn't work either Thanks in advance
Using SQL 2000 I have data in a sql table that is store in varchar like below
5.00 15.00 9.00
The integer part will never be bigger than 20. I need to move it to another SQL table that is char(5). I need the results that go in that table to like like below
05.00 15.00 09.00 I looked at the replace and cast but couldn't get the results. Any better approaches? Thanks
Hello I'm trying to write a SQL Statement along the lines of....
SELECT stringField + ' : ' + STR(decimalField) AS myField FROM tablename WHERE myCondition = myValue
Where stringField is a String field and decimalField is a Decimal Field in my Table. In this statement it converts the decimal field to a string value so that it doesn't throw a conversion error but unfortunatly it seems to round up the value to an integer value and cuts off all my decimal places.
I have a field in my database that is stored as varchar. The values are usually contain a decimal, and should have really been a float or decimal. In order for me to do analytics in my BI environment, I need to convert this to a float or decimal.
eg of values.
10.00 20.00 0.00 15.00
or could be missing when I use cast(value as float) or cast(value as decimal(9,2)) or convert(float, value) I get an error
Hello,I'm trying to decifer the data in the table that stores the data in thebinary format. All numbers are placed in varbinary fields. All I know is theMS SQL 2000 database useing collation SQL_Latin1_General_CP1_CI_AS(default).For example the content of the field is:(0xB4F5000000000000) in unicode and defined as varbinary(8).Are there any procedures that convert the unicode binary (or hexa) numbersback to ascii or readable form?I tried as following but it didn't work.select cast(0xB4F5000000000000 as decimal(8,2))Any help is appreciated,Adam
problem is that the data I am trying to insert is of the type nvarchar. eg: 06.50 I need it to be converted to type decimal (or float) before it is inserted in the new table.
Is there a way to do this within the SELECT TOP expression?
problem is that the data I am trying to insert is of the type nvarchar. eg: '06.50' I need it to be converted to type decimal (or float) before it is inserted in the new table.
Is there a way to do this within the SELECT TOP expression?
I'm trying to move records from a SQL table with a float column to a DB2 database that has the column defined as Decimal (8,2) It keeps crashing saying it has a type mismatch problem. I tried changing my source command to pass in the column already converted and it still crashes on this. I also tried doing a data conversion task to do the conversion and I still get the same error. Any ideas?
Filtrate the value in the column by using filter function..I get error message due to decimal and double.How should I convert to value 55 into double or decimal? Today, I'm using SSRS 2012.
assignment = 25 attendance = 15.5 INT_1 =0 INT_2 =0 if decimal point is 0 then have to return integer value only.. but the below query doesn't work, why?
INSERT INTO PatientVisits (PatientID,HeightInches)
select '1234-12', '68.5' -- would like to convert 68.5 to 5' 8 1/2" how would I extract the value in a select statement of '68.5' to display in feet and inches rather than a decimal value?
I am trying to convert hours and minutes to decimal and arrive at a sum of time taken. The column TotalTimeSpent is the diff in hours/mins between the Started and Ended times.
SELECT DATENAME(weekday, Started) AS Day, C.Category, ClientCode,Description, dbo.FormatDateTime(Started, 'HH:MMS 12') as Started, dbo.FormatDateTime(Ended, 'HH:MMS 12') as Ended, CONVERT(varchar,TimeTaken,108) AS TotalTimeSpentFROM dbo.Journal JLEFT OUTER JOIN dbo.Categories C ON J.CategoryID = C.CategoryIDWHERE--DATENAME(weekday, Started) =@Weekday AND Started >= @StartDate ORDER BY Day, Category, Started
All of my currency columns are only storing 2 decimal places when I insert into the database but when I pull out the data with a SELECT statement, I always get 4 decimal places instead of the 2 that were inserted. For example: Database Price SELECT statement Price 100.56 100.5600 I have tried to use the CAST and/or CONVERT commands but I cannot get the output to come out as 100.56. Has anyone had a similar problem? Thanks
I have been given some data from a Mainframe (AS400?) which has some fields coded in Packed Decimal. I have been able to load the data into a SQL2005 database table, but I now need to convert the Packed Decimal data in the binary(6) field to the appropriate integer (or float) value.
The field contains values such as the following:-
0x20202020200C
0x202020022025
0x20202020DFFA
I don't know how to interpret these. Has anyone got a function that can do this for me?
I've read several articles online that explain how packed decimal works, but none tell me how to interpret the last of my three examples. Can you help?
