Don't lose my decimals!

I have a int field and a decimal field (which however contains only numeric 
values)
I need to divide this fields:
SELECT field1/CONVERT(INT,field2)
The operation is Ok, but I don't get decimal. I tried even
SELECT CONVERT(FLOAT,field1/CONVERT(INT,field2))
but it's the same.
How do I get decimals? And I want to get only two.
Thanks.

Luciano


0
Luciano
3/12/2010 6:10:27 PM
sqlserver.programming 1873 articles. 0 followers. Follow

6 Replies
699 Views

Similar Articles

[PageSpeed] 2

"Luciano (DOC)" <lucianodoc@luciano.doc> ha scritto nel messaggio 
news:4b9a83b3$0$1110$4fafbaef@reader2.news.tin.it...

>I have a int field and a decimal field

I mean a varchar field.

L.


0
Luciano
3/12/2010 6:15:49 PM
Data type precedence determines whether your are doing integer
arithmetic. If you are doing arithmetic with integers you will not get
fractions.

If all your columns are integer, you will be doing integer arithmetic.
If any of them are float or decimal, you will get a float or decimal as
result.

You can CAST the integer columns to decimal. But the easiest way is to
use a literal decimal. Something like this:

  SELECT field1 * 100.0 / field2 AS Perc

-- 
Gert-Jan


"Luciano (DOC)" wrote:
> 
> I have a int field and a decimal field (which however contains only numeric
> values)
> I need to divide this fields:
> SELECT field1/CONVERT(INT,field2)
> The operation is Ok, but I don't get decimal. I tried even
> SELECT CONVERT(FLOAT,field1/CONVERT(INT,field2))
> but it's the same.
> How do I get decimals? And I want to get only two.
> Thanks.
> 
> Luciano
0
Gert
3/12/2010 6:19:49 PM
On Mar 12, 1:10=A0pm, "Luciano \(DOC\)" <luciano...@luciano.doc> wrote:
> I have a int field and a decimal field (which however contains only numer=
ic
> values)
> I need to divide this fields:
> SELECT field1/CONVERT(INT,field2)
> The operation is Ok, but I don't get decimal. I tried even
> SELECT CONVERT(FLOAT,field1/CONVERT(INT,field2))
> but it's the same.
> How do I get decimals? And I want to get only two.
> Thanks.
>
> Luciano

When doing math you should almost always cast your operands to the
output precision you require and take note of what BOL has to say
about math with the various types, some are not precise.

DECLARE @INTFIELD INT =3D 22;
DECLARE @DECIMALFIELD	DECIMAL =3D 23.23;

SELECT CAST( CAST(@INTFIELD AS DECIMAL(6, 2)) / CAST(@DECIMALFIELD AS
DECIMAL(6,2)) AS DECIMAL(6, 2));



0
Tom
3/12/2010 6:38:56 PM
"Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> ha scritto nel 
messaggio news:4B9A85C5.D2F17BC1@xs4all.nl...

> If any of them are float or decimal, you will get a float or decimal as
> result.

I got it.

> You can CAST the integer columns to decimal. But the easiest way is to
> use a literal decimal. Something like this:
>
>  SELECT field1 * 100.0 / field2 AS Perc

Both solutions are ok for me. I tried both.
I have two more questions if you don't mind:-)
1) how do I truncate the number at second decimal and eventually rounding 
it?
2) what is that numeric form (100.0) and where can I get informations about 
it?
Thanks.

Luciano


0
Luciano
3/12/2010 6:43:48 PM
"Luciano (DOC)" wrote:
> 
> "Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> ha scritto nel
> messaggio news:4B9A85C5.D2F17BC1@xs4all.nl...
> 
> > If any of them are float or decimal, you will get a float or decimal as
> > result.
> 
> I got it.
> 
> > You can CAST the integer columns to decimal. But the easiest way is to
> > use a literal decimal. Something like this:
> >
> >  SELECT field1 * 100.0 / field2 AS Perc
> 
> Both solutions are ok for me. I tried both.
> I have two more questions if you don't mind:-)
> 1) how do I truncate the number at second decimal and eventually rounding
> it?
> 2) what is that numeric form (100.0) and where can I get informations about
> it?
> Thanks.
> 
> Luciano

Use ROUND() to round the result, for example:

  SELECT ROUND( field1 * 1. / field2, 2) AS TwoDigitsRounded

If you want to truncate, then use the 3rd parameter of the Round
function. For example:

  SELECT ROUND( field1 * 1. / field2, 2, 1) AS TwoDigitsTruncated

The "100.0" is a literal number. Since it contains a decimal point, SQL
Server gives it the decimal data type. I have use the minimal form of it
in the examples above, a number with a dot without any fraction.

If you don't want to multiply with a literal, you can cast any of the
two integers, like this:

  SELECT ROUND( CAST(field1 AS decimal(10,2) / field2, 2)

-- 
Gert-Jan
0
Gert
3/12/2010 9:09:31 PM
"Gert-Jan Strik" <sorrytoomuchspamalready@xs4all.nl> ha scritto nel 
messaggio news:4B9AAD8B.DE2852D3@xs4all.nl...

[cut]

Thank you very much for your explanations!

Luciano 


0
Luciano
3/12/2010 10:06:41 PM
Reply:

Similar Artilces:

Outlook Client Is Losing Connection To Microsoft CRM While Online
Hi. I have Rollup 2 installed on both the Outlook Client and the server. On my laptop, I am running Win XP Pro with SP 2 and all the updates applied and Outlook 2003 with all the updates applied. I am noticing that I seem to be losing the connection to Microsoft CRM from the Outlook Client. Right after I boot my laptop I am able to access to Microsoft CRM from the Outlook Client for between 4 hours to a day. After that if I click on the Accounts, Contacts or Leads folders nothing seems to happen. I have waited up to 10 minutes after clicking and nothing. At this point I can still use...

Why do I lose contacts?
I enter a contact and a few days later (after a reboot) it's gone? Sometimes I show 80 contacts. Sometimes 1518? What is up with that? On Sun, 29 Nov 2009 22:12:01 -0800, Davis Straub wrote: > I enter a contact and a few days later (after a reboot) it's gone? > > Sometimes I show 80 contacts. Sometimes 1518? What is up with that? Not a clue. I ran Windows Live Mail without signing in to Windows Live Services for three, or four days. My Contacts list never changed, or disappeared during that time. It is, however, different than the Contacts list I normally see ...

SS#'s lose lose formatting in .csv
Hi, I have a user who is entering a list of ss numbers. She has to have them in this format: 024574882 712894748 First off, we can't get the leading 0 to stay, it just disappears. I have tried formatting the column as text, as numbers, as custom #########, and like '983474744. No matter what we do, after saving and opening it again, the leading 0's are gone and some number turn in exponential notations. Can someone please help?? This should be an easy fix, but it seems the formatting I put on this does not hold!! thanks in advance.....Bill Select the column Either cl...

losing pictures
I had produced a 3 fold brochure and printed it successfully for months. Then one day 2 of the jpg files stopped printing. I have reloaded Publisher, done everything I can think of, but can not get those files to print on the office color printer. They will print on the Black and white. The brochure will print from someone else's computer!!!! if I save the file on a CD and print it from someone who has Publisher. I talked to Gateway and they said it was my problem, this is a new 2003 computer, with 2002 Publisher. Can anyone help Update your printer driver from the website of your prin...

Lose Focus
Env: VC++6.00 I want a control on a Dialog to be losed focus after it is disabled by EnableWindow(FALSE). I don't care who will get the focus, so I don't want to use SetFocus(). Is there a way? TIA William >I want a control on a Dialog to be losed focus after it is disabled by >EnableWindow(FALSE). >I don't care who will get the focus, so I don't want to use SetFocus(). > >Is there a way? William, Try using CDialog::NextDlgCtrl or maybe PostMessage WM_NEXTDLGCTL to the dialog. Dave Thanks Dave. That is exactly what I want. William > Try using CDial...

Losing online passwords
Money 2001. Recently, when I connect to online services, the password for my Discover Card account is sometimes missing. The other online banks remember their passwords. I always re-enter it and check "remember passwords", but about every 6 times I log on, the Discover password is missing. I just recently installed XP. Can anyone explain? I have noticed the same problem on my 98SE Money 2004. Must be on Discover's end. "OhMarty" <ohmarty@tampabay.rr.com> wrote in message news:%23OVhbJ6KEHA.3292@TK2MSFTNGP11.phx.gbl... Money 2001. Recently, when I connect to o...

Changing ISP / email address
My friend is about to change ISP and get a new email address. He doesn't use webmail only Outlook. He has many folders containing emails he wants to keep, and also all his contacts. Can someone please tell me, if he changes email address will all the folder contents and contact details remain in Outlook? Are they on his hardrive or not? Thanks! Outlook data is stored locally. If he uses the same data file all his information will be there. -- Russ Valentine "Alfa Mum" <Alfa Mum@discussions.microsoft.com> wrote in message news:54C0F148-C087-4A8A-A25C-A...

You will find that with a quartz watch, you will on average lose or gain up to one second per week. Compare that to a mechanical watch, which can potentially lose or gain up to one minute per week.
You will find that with a quartz watch, you will on average lose or gain up to one second per week. Compare that to a mechanical watch, which can potentially lose or gain up to one minute per week. It is obvious that quartz watches are much more accurate and reliable than a mechanical watch. What makes quartz so accurate is its extremely high oscillations of up to 32,768 cycles per second at・3校salereplicawatch.com ...

decimal places
My apolgies if this is entered twice, not sure if i did it the first time. I need to format numbers in field1 according to the value of field2 ie if field1 is 12.0433 and field2 is 7, then i need to see displayed 12.0433000, and if field1 is 12.0433, and field2 is 2, then i need to see displayed 12.04 any ideas greatfully received pat Answered in another group where you posted this q. ...

Trapping losing commands
In our applications once in a while the commands don't get through to the SQL Server for different reasons. Since it is an automatic process there is nobody in the front end to rerun those command, and they are getting lost. The rezones for the lost could be: Time out, broken connections, Deadlocks ect. Is there a way SQL can trap these and replay them? Gal (Gal@discussions.microsoft.com) writes: > In our applications once in a while the commands don't get through to > the SQL Server for different reasons. Since it is an automatic process > there is nobody in th...

Inserting Rows...but losing "FORMAT"
I have a spreadsheet that I have used for 5 years...with daily data being inputted (in newly inserted rows), then Grand Totaled at the bottom of the spreadsheet. I have never had a problem...UNTIL RECENTLY. One of my columns is the time of day, such as 2:54pm, and presently, all of my inserted rows, except for only 3 of them, depict the time as 2:54 (WITHOUT THE AM or PM), so, I use the Format Painter, and I Fix the problem...that is, until the next day, when the same incident again occurs. I do not know why this is happening, but I must use this extra step (Format Painter) ...

losing a toolbar
I'm trying to setup my default workbook/sheet. Everything is working so far, except I cannot get rid of the standard toolbar. It keeps coming back. I do I get it out of my default preferences? thanks! Hi there; Right click on any toolbar and unchech the checkbox for the toolbar you don't want to see anymore. Save the file, close Excel and the next time you start it, it must not be visible anymore. Mark. More Excel ? www.rosenkrantz.nl or contact@rosenkrantz.nl -------------------------------------------------------------------- "shank" <shank@tampabay.rr.c...

Decimal Places
Hi, I am currently having a problem with decimal places. Basically I don't want any whatsoever. In a cell, I am doing division between 2 figures to find out how many items I can purchase with an amount of money. So, the formula is AmountOfMoney/CostOfItem If the ansewr was for example 63.97 then the results cell would show 64 (I have it set so that 0 decimals are shown) There are then other calculations throughout the spreadsheet that use this result. However, since I can only afford 63 WHOLE items, I don't want the 0.97 to be included as part of the equation since this thr...

Losing Sound
i encountered this problem more frequently now, after listening to music or video thesound is suddenly muted and i need to restart XP to get back. any suggestions? thank you If the pc has an add-on sound card,open device mgr,expand the sound tree,R.click all the sound card listings (one at a time),select "uninstall", do not restart pc after each,once thru,exit device,shutdown pc.Move the sound card to another PCI slot,start pc,xp will reinstall it..If its on-board, do the same w/o the card move.Also,one can go to run,type: DXDIAG Run the Direct X sound tests.. ...

SBS 2008 keeps losing default gateway
Strangely enough "route print" shows a default route, but the default gateway is missing in the network connection properties, and I have to manually reenter it to restore Internet connectivity. Anyone got any ideas what's up? Possibly related is that the Internet router actually tries to proxy ARP for all IP addresses, which rather confused the configuration wizard. If it happens again, open regedit, navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\Interfaces\<GUID>\DefaultGateway and double click the DefaultGateway value....

Outlook loses graphics
I receive an email with background, pics, special fonts, etc. But when I forward it, it goes out plain text, with pics as separate attachments. All formatting stripped. How can I send out what I received, in same format? Thanks ...

Decimal Values (currency) when using scroll bars
Im trying to create a decision making tool that uses a slider control to change a cost per unit field. I used a scroll bar from the Forms toolbar. However, this control is not able to increment decimal values. Because I am using currency, I need .01 detail, however, any decimal value in the Incremental change field in the Control tab of the format control for the slider is replaced with 0. Ive currently linked the control to another cell and the relevant cell has a /100 formula, but now my users cant type a value directly. Id appreciate any help. Thanks. Hope it...

losing spreadsheets
Hi y'all, At work we're sharing a spreadsheet with everyone saving on a regular basis so everyone can see the updates. Unfortunately, the spreadsheet keeps getting damaged. Is this most likely due to someone doing something not so bright? or Is this most likely due to someone's computer dying? or Can "saving as" (say I make a copy for my own personal use because of all the crashes) a different sheet cause it? Thanks for any help regarding this situation. Merry Christmas Robert Newton Robert I have to say that my experience with sharing Excel has always been ...

count decimal place
I want to query a field for more than 3 decimal places. A ..23 1.56 49.34534 7.434 I want 49.34534 to be the only result. Any thoughts? SELECT MyField FROM MyTable WHERE (Len(MyField) - InStrRev(MyField, ".")) > 3 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "samuel" <samuel@discussions.microsoft.com> wrote in message news:B513BEA8-8FB3-4471-ACB9-0D1D5FE4A117@microsoft.com... > can you expound for me? > > "KARL DEWEY" wrote: > >> Convert to string and using InStrRev count to find pe...

Convert Decimal to Percent Within Text String
Hello everybody, I have a text string in Excel that contains percentage values taken from different cells. However, the text string reads the percentage value and outputs it as a decimal: Percent defects of 0.678276052493135, 0.674664937815204 and 0.57388331030011. Here is the code: ="Percent defects of "&N2&", "&N3&" and "&N4&"." (Cells N2, N3 and N4 contain percentages) I hope I'm not missing a very simple solution here. Thanks! Brian Hi Brian, ="Percent defects of "&TEXT(N2,"0.00%") etc ...

Budget Maintenance Import rounds to 2 decimals
My source Budget data comes with 4 decimals, but when I import it to GP, the decimals are rounded to only 2. Is there a way to change that? Thanks, -- Hector Herrera Business Systems Analyst II Northwestern Medical Faculty Foundation Chicago, IL USA ...

Losing citations and sources when using track changes and merging
Whenever I use track changes, share my documents between people, get the multiple files back, and merge all files all my citations and sources are lost, and it's a pain to re-enter them. What's going on? ...

losing emails
Once an email is read, it disappears. Not into deleted mail folder--it just disappears View | Current View. You want: Show All Messages, not Hide Read Messages. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "sfish714" <sfish714@discussions.microsoft.com> wrote in message news:A40643C3-1D22-4B7E-B792-8B72C6FAE117@microsoft.com... > Once an email is read, it disappears. Not into deleted mail folder--it > just > disappears "sfish714" <sfish714@discussions.microsoft.com> kirjutas sõnumis news:A...

How do I merge two columns without losing data?
If I want to split a column in two, I know I can use the Text to Column feature. How can I go from two columns to one without losing data in the merge? One way =CONCATENATE(A1,B1) Ian --- "Caseybay" <Caseybay@discussions.microsoft.com> wrote in message news:2D860F83-00D5-446D-AAA5-BE938DF3C1BF@microsoft.com... > If I want to split a column in two, I know I can use the Text to Column > feature. How can I go from two columns to one without losing data in the > merge? =a1&b1 or =a1&" "&b1 (use a helper column of formulas.) Caseybay wrote...

Decimals in Excel
Hi , I am using P0 and N0 to get rid of decimals and they are still appearing in excel ? Any suggestions ? Regards Malcolm ...