Data Type

[NoKeS]

I want to insert a datatype real in a table only in this format.
Ex: I want to insert 1.05, but when i make a select it showm me 0.47999998927116394
how do i do to apper the value that i insert?? withot the rest of the numbers?
URGENT!

 

[RickCole]

Try using the decimal datatype rather then real.

Rick.

 

[NoKeS]

I've already tried to use decimal but now when I insert 0.48 it show me the result 0. What do I have to do? the code is:
Create Table Moeda
(
Data Datetime,
Moeda varchar(10),
Factor decimal,
CONSTRAINT Data_Moeda PRIMARY KEY (Data, Moeda)
)
Drop Procedure InserirMoeda
Create Procedure InserirMoeda
(
@Data Datetime,
@Moeda varchar(10),
@Factor decimal
)
AS

Insert into Moeda
VALUES (@Data, @Moeda, @Factor)

declare @data datetime
select @data=getdate()
exec InserirMoeda @data,'PPP',1.48

 

[tlbroadbent]

You must declare the precision and scale of a decimal data type.

col1 decimal(10,2) --10 digits with 2 after the decimal

FYI: Float and Real are approximate numeric date types. They are stored in a binary representation and many decimal values cannot be represented precisely. That is why float doesn't show the precise number of digits you tried to store. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[RickCole]

This is whay you need note that the the decimal field is now Factor decimal (10,2).

Create Table Moeda
(
Data Datetime,
Moeda varchar(10),
Factor decimal (10,2),
CONSTRAINT Data_Moeda PRIMARY KEY (Data, Moeda)
)
Drop Procedure InserirMoeda
Create Procedure InserirMoeda
(
@Data Datetime,
@Moeda varchar(10),
@Factor decimal (10,2)
)
AS

Insert into Moeda
VALUES (@Data, @Moeda, @Factor)

declare @data datetime
select @data=getdate()
exec InserirMoeda @data,'PPP',1.48


Rick.

 

[NoKeS]

and if i want to put in one field the value 1.053 and in another 0.48 it does'nt work... i show me in one 1.05 and in the other .48 I have to do this work until 6.00 pm. URGENT! Really Tanks.

 

[NoKeS]

and if i want to put in one field the value 1.053 and in another 0.48 it does'nt work... i show me in one 1.05 and in the other .48 I have to do this work until 6.00 pm. URGENT! Really Tanks.

 

[RickCole]

When you create the field Factor decimal (10,2) It will allow 10 figure before the decimal point and 2 after it.

In you latest example you would need to do the following Factor decimal (10,3) to fit in 1.053 i.e. 10 before the point and 3 after.

Rick.

 

[NoKeS]

Yes but next when i insert the value 0.57 it show me 0.570 and i want 0.57

 

[RickCole]

To do what you require you will have to use the CAST or CONVERT functions to manipulate the data when it is returned with a select statement.

Might be easier if the trailing zeros could be trimmed off before they are inserted??

Rick.

 

[NoKeS]

Can you send me the code to do it please? I'm a newbie in SQL, and i'm serve as apprentice, and i have to do this work! Can you help me please?

 

[RickCole]

Nokes,

Not knowing how you are going to use this , amount of data involved, what your trying to achieve etc ,etc. It's a bit difficult to work out what you really need.

See my example below: Put together very quickly so not the the neatest of solutions but I think it will work.

Create Table Moeda
(
Data Datetime,
Moeda varchar(10),
Factor1 decimal (10,4)
CONSTRAINT Data_Moeda PRIMARY KEY (Data, Moeda)
)
Drop Procedure InserirMoeda

Create Procedure InserirMoeda
(
@Data Datetime,
@Moeda varchar(10),
@factor1 varchar (10)

)
AS
If substring (@factor1,10,1) = '0'
select @factor1 = substring (@factor1,1,9)

If substring (@factor1,9,1) in ('.','1','2','3','4','5','6','7','8','9')
goto finish




If substring (@factor1,9,1) = '0'
select @factor1 = substring (@factor1,1,8)

If substring (@factor1,8,1) in ('.','1','2','3','4','5','6','7','8','9')
goto finish



If substring (@factor1,8,1) = '0'
select @factor1 = substring (@factor1,1,7)

If substring (@factor1,7,1) in ('.','1','2','3','4','5','6','7','8','9')
goto finish




If substring (@factor1,7,1) = '0'
select @factor1 = substring (@factor1,1,6)

If substring (@factor1,6,1) in ('.','1','2','3','4','5','6','7','8','9')
goto finish




If substring (@factor1,6,1) = '0'
select @factor1 = substring (@factor1,1,5)

If substring (@factor1,5,1) in ('.','1','2','3','4','5','6','7','8','9')
goto finish




If substring (@factor1,5,1) = '0'
select @factor1 = substring (@factor1,1,4)

If substring (@factor1,4,1) in ('.','1','2','3','4','5','6','7','8','9')
goto finish




If substring (@factor1,4,1) = '0'
select @factor1 = substring (@factor1,1,3)

If substring (@factor1,3,1) in ('.','1','2','3','4','5','6','7','8','9')
goto finish




If substring (@factor1,3,1) = '0'
select @factor1 = substring (@factor1,1,2)


If substring (@factor1,2,1) in ('.','1','2','3','4','5','6','7','8','9')
goto finish


finish:

Insert into Moeda
VALUES (@Data, @Moeda, @factor1)

 

[tlbroadbent]

Here are some clarifications and corrections.

If you declare a column as data type DECIMAL(10,3) you get 10 total digits with 7 before and 3 after the decimal. You are defining the way the value is stored. You can't trim zeros, leading or trailing when storing the data.

Enterprise Manager, Query Analyzer and other client tools determine how to display the values. Typically, leading zeros are dropped. Trailing zeros are not. You can format the data for display but must be careful about the conversions you do. If you convert a numeric value to character type data, you may not be able to perform calculations and sort orders will likely change.

If you must maintain the data exactly as formatted, then store it as character type data. Then if you need to do calculations, convert the character data to numeric. This is highly irregular and inefficient. However, if the overriding criteria is maintaining the format as input, I don't see that you have much choice. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[mukund]

Hi Nokes,
You can try using STR function
for e.g.

select str(PoTotal,12,2) from POHead

Where POTotal is a float data type.

12 is no. of places alloted for the PoTotal output
and 2 is the no. of places after decimal point.

Mukund.