Trimming Decimals

Aug 17, 2005

I have a field in my SQL table that is defined Decimal(10,8), which
provides me with the ability to have up to 8 digits after the decimal
place, however, if I store a value such as 3.14, it is stored as
3.14000000. This is fine in the database, but it is the same when it's
returned. I have tried using Convert.ToDecimal on my returned row but
it doesn't work. The value at runtime appears as 3.14D in the locals
window but displays with all the insignifcant digits.

Any ideas what's happening or if there is an easy way to trim the trailing zeros?

View 2 Replies

Trimming

Mar 31, 2008

Hello all, i'm new to using sql server. i was wondering if i anyone can assist me with the following:

M5 - AJJJJJJJ

How can i just get the '5' in another column? Thank you.

View 20 Replies View Related

Trimming A DateTime

Mar 16, 2004

Hi,

Probably easy but I'd like the best way to do it:

I have a datetime column with A LOT of rows. I'd like a select statement to return each date with everything after the hour part set to zero.

For example, if my column has the following values:

2004-11-03 13:03:36.876
1999-02-17 01:56:12.126

Should return:

2004-11-03 13:00:00.000
1999-02-17 01:00:00.000

I know I can do this with a function and a couple of casts but there must be an easier (and faster) way!?!?

Thanks,

Skip.

View 1 Replies View Related

Trimming Spaces

Mar 9, 2006

I need to trim what looks like two spaces from a field.

Example:

" 601274" needs to be "601274". Does anyone know the syntax?+

View 9 Replies View Related

DTS Trimming My Data

Jul 20, 2005

HiI have a DTS package that pulls data from oracle and inserts it into SQL.During this transfer, any data that has trailing spaces, loses those spacesin SQL. i.e. it's been trimmed.Any way to set DTS not to trim the data ?ThanksSteve

View 1 Replies View Related

Trimming The Numbers.

Jul 19, 2006

Hi

I have a question regarding the trimming of some numbers at the end of a column

I have a resource table containing the name column for Ex : Santhosh, Kumar Edukulla X21646. Is there any way we can find the way to remove the numbers at the end from this column and copy to a new column in SQL SERVER.

Thanks in Advance!

Santhosh

View 1 Replies View Related

Trimming Characters

Apr 14, 2006

I have a field called sku in a table... many of the sku fields have data as ep12345678. I need a sql statement that will update all data in that field from ep12345678 to 12345678.

So I need to trim off the 'ep' Any one know how to do that?

View 8 Replies View Related

Trimming Leading Zeros

May 31, 2007

mssql 2000, asp.net(vbscript)
How am i able to trim leading zeros? Right now i have two values:00000005 500000010 1000000015 15..... etc...
how do i write a query where i can select an argument where 5 = 0000005?
the column with 00000005 is varchar and5 is numeric

View 4 Replies View Related

Trimming Phone Number

Apr 10, 2008

Hi all,

i have a table in SQL with +- 5000 phone numbers in various styles
e.g.
0515 - 578989
033 2981861
+31 (0)30 602 44 44
03.28.68.63.18
03/658.85.80

I want this to convert to international format like this
003237778899
0031306024444

Do i have to write 10 replace after each other or is there an easier way to format the phone numbers?

Thx in advance!!

View 8 Replies View Related

Automatic Trimming Transformation

Mar 9, 2006

I would love to see an automatic trimming transformation. We currently have 14 different ERP systems throughout our company and almost everything we do, when it comes to strings, requires trimming. Although doing it through the derived column task is somewhat easier, it is very time consuming and error proned, if you overlook a column or two.

-BradG

View 2 Replies View Related

Stop Cell From Trimming The Value

Jan 19, 2007

Hi,

I am adding some empty spaces to the string value but it comes out trimmed.
How can I fix that?

Thanks,
Igor

View 2 Replies View Related

Trimming Space In All Database Fields?

Nov 29, 2007

I have an SQL 2000 database with several tables that are populated using various forms. One or more of the forms is causing the several fields to fill in with data input plus empty spaces to the right for the full character length assigned. Is there a routine or database feature in Visual Studio that can trim off all trailing spaces in all fields in a database table? Is there a compacting routine that might be called?
Thank you

View 6 Replies View Related

SQL Trigger For Trimming String Fields

Jul 20, 2005

greetings!! the below trigger works fine in SQL 2000 and doesn't takeup much resources, it's a very simple solution to trim text fields atinput in SQL. I know a lot of folks will say to create input masks atthe UI level and that SQL is a restricted back-end DB, but to be quitehonest I don't trust those UI guys and as a DBA I will have to cleanthe mess when they forget to validate. Hope it can be usefull to anyof you too. ;)----------------------------------------------------------------------CREATE TRIGGER [TRIMMER_TGR] ON [dbo].[MyTable]AFTER INSERT, UPDATEASIF UPDATE (MyStringField)DECLARE @TRIMMEDFIELD NVARCHAR(50)DECLARE @MYID INTSELECT @TRIMMEDFIELD = MyStringField from InsertedSELECT @MYID = ID from InsertedBEGINUPDATE tblDocket SET MyStringField = RTRIM(LTRIM(@TRIMMEDFIELD))WHERE ID = @MYIDEND

View 6 Replies View Related

Help For Trimming Leading And Trailing Spaces

Jan 7, 2008

Hi
Is there any way to trim all the leading and trailing spaces in all the column in a table.
JigJan

View 5 Replies View Related

Trimming New Line From Text String In SSIS Package

Jun 7, 2007

Hi,

I want to trim new line from string in sql server 2005...

I prepared one dtsx package and in that I want to trim new line from one of my column but i am unable to do that.

for example:

I want to retrive one column name DESCRIPTION and put that in text file but along with it I am getting blank space and new line also that I want to trim...

T.I.A

View 1 Replies View Related

Bcp And Decimals

Oct 25, 2000

I am trying to use bcp to load a text file into SQL Server 7. The decimal numbers in the text file are formatted as 123455 and when I load the values to SQL I want the last two digits to be the decimal 1234.55. My current process is loading the value as 123455.00. What do I need to do in the format file to get this to work???

View 2 Replies View Related

Help With Decimals

Feb 22, 2004

Hi all,
This is what I have:
DECLARE @OnTime int
DECLARE @UnControlled int
DECLARE @Volume int
DECLARE @GrossEffect decimal(10,2)
DECLARE @NetEffect decimal(10,2)
DECLARE @WeekEndDate datetime

SET @OnTime = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE OnTimeFlag = 1 AND ARSScanType = 'D' AND ARSType='AN')
SET @UnControlled = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE ControlFlag = 'U' AND ARSScanType = 'D' AND ARSType='AN')
SET @Volume = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE ARSScanType = 'D' AND ARSType='AN')
SET @GrossEffect = (@OnTime/@Volume * 100)
SET @NetEffect = ((@OnTime + @UnControlled)/@Volume * 100)
SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblEDITempARS)

INSERT INTO tblSummaryData2(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate)
VALUES ('AN',
@Volume,
@NetEffect,
@GrossEffect,
@WeekEndDate)

My numbers are:
OnTime=8089
Uncontrolled=6
Volume=8095

The GrossEffect comes as 0 and the NetEffect=100
AN809501002/21/2004

Why is my GrossEffect = 0??? it should be 99.9

View 1 Replies View Related

Help With Decimals

Feb 19, 2008

Hello, I have a table with a field with the format of 8 decimals. When I make a select to that table instead is given me (for example) 25,00 it gaves me 25,00000000. How can I fix the with the correct format? Thanks in advance.

View 2 Replies View Related

Decimals In SQL Server

Jul 7, 2005

I need to display decimal results from SQL Server queries, but the decimals keep getting truncated. For example, 9 divided by 4, keeps getting truncated to 2. Does anyone know how to display the whole result, including the decimals?

View 2 Replies View Related

Decimals In View

Aug 24, 2004

Hello,

I want a view to always present my numeric fields with 2 decimals. In my table I have the following values in field "amount" (numeric(18,2))

181.25
176.5
170

I want the view to show
181.25
176.50
170.00

I have tried Cast but that doesn't seem to do the job.

Help!

Rolf

View 1 Replies View Related

No Decimals In Query

Jun 24, 2008

in quantity column - i have

100.00000

but i want only 100

no decimals..

how to get that?

thanks

View 2 Replies View Related

DECIMALS IN QUERY

May 31, 2007

I have two fields whose format in the table is decimal (14,5).
I now have to divide one field by the other, and I want the result
to be in the format decimal (21,16).
Whatever I do I'm not able to get this result with sixteen decimals.
I tried with dec(Field1,21,16)/Field2 but it yields six decimals.
I tried with dec(Field1,21,16)/Field2 but it yields an integer.
I tried other ways but I always get a result which is made of maximum
six decimals.
What can I do?
Thank you.
Anna - Verona (Italy)

View 5 Replies View Related

Truncated Decimals

Jan 16, 2006

I am relatively new to SQL server. I am tring to send some decimalvalues to the database using a stored procedure with parameters of typeDECIMAL. Every time it inserts the values into the database thedecimals are truncated. I saw on the MSDN library that you have to setthe precision and scale values b/f you run the stored procedure. So Iset the precision to 8 and the scale to 4 and it still didn't help. Cananyone help me?

View 6 Replies View Related

Decimals With Sum Agregate Function

Feb 23, 2001

I'm using the sum() agregate function inside a select with a decimal field with an scale of 2.
I want to get the result always with 2 decimals, but if the result is 100.10 I get 100.1 . I've tried
to convert and cast the result but I can't find how to get 2 decimals. Please help. Thanks

View 1 Replies View Related

Updateing DECIMALs With DB-Library

Aug 18, 2004

Hello,

I have encountered a problem doing an update on a Field defined as DECIMAL with dbcursor().

I have opened a cursor with dbcursoropen() with option LOCK_CC set. Then I have bound some char variables to CHAR Fields with STRINGBIND and some double Variables to the DECIMAL fields with FLT8BIND.

When I read the cursor, the data is correct in the bound variables. But when I do an update, all data for the numeric fields are gone and the database contains only NULL Values. The Strings are updated correctly.

I have tried to set the length, I am using the poutlen-variables with a value other than 0, but nothing helps.

Does anyone have an idea about what can I do?

Thank You in advance.

Frank

View 3 Replies View Related

How To Add Trailing Zeros 2 Decimals

May 18, 2008

In my select query for field ordernumber want to add two trailing zeros in the resultset, how can i add.

Thanks for the info.

View 1 Replies View Related

How To Add Trailing Zeros If There Is No Decimals

May 19, 2008

How can i add two trailing decimals if there is no decimals:

for example if it is just 1, then make it 1.00

if it is 1.12 then leave it as it is.

can you please help.

select cast(ordernumber as varchar(10)) + '00' from ordertable

Thanks for the info.

View 1 Replies View Related

Doubt In Truncating Decimals..

May 22, 2008

Hello

I need a help ..
I need to convert 45.4593251 to 45.46

How to achieve it.
Can any one help me please..

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

View 3 Replies View Related

Problem Saving Decimals

Nov 10, 2005

I want to save a decimal in my sql table. It's an hourly rate. I have my field as smallmoney type. I am updating the table using a sp from my web page. It always rounds my entry up eg 3.75 becomes 4.00. Heres my sp and the line where I am passing the parameter to the sp.

CREATE PROCEDURE spRB_AmendRoomData

@strRoomId int,
@strRoomRef nvarchar (50),
@strFloor nvarchar (50),
@strZone nvarchar(10),
@strLocation nvarchar (100),
@strType nvarchar(50),
@strNo int,
@strDirections nvarchar(2000),
@strPhoneNo nvarchar (50),
@strMaxNo int,
@strEquipmentFac nvarchar(2000),
@strNotes nvarchar(2000),
@strCharge smallmoney,
@strNotInUse bit,
@strIntExt nvarchar(20)

AS
BEGIN
UPDATE tblRB_Rooms SET

RM_RoomRef=@strRoomRef,
RM_Floor=@strFloor,
RM_Zone=@strZone,
RM_Location=@strLocation,
RM_Type=@strType,
RM_No=@strNo,
RM_Directions=@strDirections,
RM_PhoneNo=@strPhoneNo,
RM_MaxNo=@strMaxNo,
RM_Equipment_Facilities=@strEquipmentFac,
RM_Notes=@strNotes,
RM_Charge=@strCharge,
RM_NotInUse=@strNotInUse,
RM_IntExt=@strIntExt

WHERE
RM_RoomId = @strRoomid

END
GO

Cmd.Parameters.Add(New SqlParameter("@strCharge", Me.txtCharge.Text))

Thanks for any help

View 4 Replies View Related

Find Number Of Decimals

May 14, 2008

Can someone suggest a FAST way to select Currency values where the number has more than X decimal places? There are zillions of rows so looping in code is not the preferred solution.

(a bug forgot to round)

Thanks for your help

View 4 Replies View Related

Need An Expression Format To Put Decimals .00

Mar 27, 2007

I have the following field(Fields!SequenceNO.Value), coming form database.

i would like to have an expression IIF...

if the value coming from database is just 1, then make it 1.00 or

if the value coming from database has a period or point say 1.01 then show as it is.

thank you very much for the help / information....

View 1 Replies View Related

Transact SQL :: Two Decimals Multiply Each Other Become Zero

May 18, 2015

Script as follows :

select cast( 0.0050000000 as decimal(38,23)) * cast(0.0000010000 as decimal(38,23))
select cast( 0.0050000000 as decimal(28,23)) * cast(0.0000010000 as decimal(28,23))
select cast( 0.01 as decimal(28,15)) * cast(0.0000000001 as decimal(28,15))
select cast( 0.01 as decimal(28,16)) * cast(0.0000000001 as decimal(28,16))

The result was following：

0.0000000--was zero
0.000000005000000000000000000
0.00000000000--was zero
0.0000000000010

View 3 Replies View Related

Problem Rounding Decimals

Apr 15, 2008

Hi,
Im having a decimal value of 1.24
im rounding off the value to 1 precision
ex:- select round(1.24,1)
the output its returning is 1.20
i want to get only 1.2
can anyone suggest on this??

View 3 Replies View Related