converting decimal to time

how do you convert a numeric to time format if it shows hours but a decimal figure for Minutes. For example if I have hours in decimal format like this

28.5000

but I want to show it in this format:

28:30:00

where 28 = hours, 30 = minutes, 00 = seconds

Thanks.This is a presentation issue. It should be handled at the client, not the server. Dealing with formatting inside the database is just a recipe for problems later.

-PatP|||What Pat said

DECLARE @.hours decimal(15,4)
SELECT @.hours = 28.5
SELECT RIGHT('00' + CONVERT(varchar(2),FLOOR(@.hours)),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@.hours-FLOOR(@.hours))*60))),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@.hours-FLOOR(@.hours))*60)-FLOOR(((@.hours-FLOOR(@.hours))*60)))*60),2)|||Displaying it as 28:30:00 is a presentation issue, but converting it to a valid datetime format falls within the scope of the database server:declare @.Hours decimal (6, 4)
set @.Hours = 28.5
select dateadd(minute, @.Hours * 60, 0)|||I was gonna give them that, but I realized it wasn't what they asked for...

Hours of what BTW...sounds like derived data gotta be careful with that

Converting Decimal to String W/O Decimal Point

I'd like to convert a Decimal value into a string so that the entire
original value and length remains intact but there is no decimal point.

For example, the decimal value 6.250 is selected as 06250.

Can this be done?select replace(cast (6.250 as varchar),'.','')

JD wrote:
> I'd like to convert a Decimal value into a string so that the entire
> original value and length remains intact but there is no decimal
point.
> For example, the decimal value 6.250 is selected as 06250.
> Can this be done?|||select replace(cast (6.250 as varchar),'.','')|||but length doesn't remain intact|||but length doesn't remain intact|||select replace(space(1)+replace(cast (6.250 as varchar),'.',''),' ','0')|||Thanks!!!

Converting decimal numbers to Words

Hi there all the Gurus,
I am trying to mave a convert a decimal number ie. 1230.30 to words in my report and i am getting result with the 30/100 at the back of the words.
Is there any way i can get a result of :
'ONE THOUSAND TWO HUNDRED THIRTY AND THIRTY'Well, I guess you could split the number into two parts (at the decimal point) and convert each separately.|||UpperCase(TOWORDS(integer({INVOICE.AMOUNT}))) + Uppercase(TOWORDS(fraction({INVOICE.AMOUNT}))) + 'CENTS ONLY'

i have tried this formula but it keeps prompting error "Missing '('

Please advice|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');|||Cool! Many thanks for the solution.|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');

Hi, if I want to convert a Sum of Amount into English Words? How to do this?|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');

Hi there,

If i am not wrong, you must create a formula or using the SUM maths function to get the total amount then put it replacing the (12345.67)

or we shall hear what's the Guru says :)|||Hi there,

If i am not wrong, you must create a formula or using the SUM maths function to get the total amount then put it replacing the (12345.67)

or we shall hear what's the Guru says :)

Yap, I have already tried in this way, which replace the 12345.67 into sum(@.amount), yet it doesn't work.

local stringvar sNumber := cstr(sum({@.Amount}));
local numbervar whole := truncate(sum({@.Amount}));
local numbervar pos := instrrev(sNumber, '.');

local numbervar decimal := 0;
if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Ringgit and ' + ToWords(decimal, 0) + ' Sen Sahaja');|||What error do you get? Presumably 'This field cannot be summarised' when you run the report as you can't sum a formula {@.amount}.
Try summing something that can be summed like the underlying database value, or maybe {@.amount} is already your summed value?sqlsql

Converting Data Types

I have a table with a field of Char(10) Data type

this field contains records of work time Attendance in a decimal format

Ex. I attend today for 8.30 this means eighthours and thirty mintutes

the main problem faced me to make some calculations on those records (sum, subtract, etc)

so I want to convert the data from char type to decimal or real using the next code but it doesn't work

select (cast (satreg, decimal) + cast (satot, decimal)) from timecard

can u please help me in the main issue how to sum char type records or at least how to convert them to decimal

Regards

create table test

(

time char(10)

)

insert into test (time) values ('8.30')

insert into test (time) values ('9.30')

insert into test (time) values ('10.30')

selectconvert(decimal(10,2), time )

fromtest|||

this can help:

it breaks your 8.30 to 8 and 30

SELECT substring(test,0, (patindex('%.%',test))),substring(test,(patindex('%.%',test)+1),len(test)) from test

Converting data question

I need to combine two columns and copy them to another. One column is varchar and one is decimal (3,0) and the destination column is int. The column of decimal contains only 1 and 2 digit numbers. I need to perface a 0 to the data with 1 digit before combining the two columns. If someone could guide me here, I'd appreciate it. I've been trying to use cast and convert with no success.Your destination column should be a varchar column if the column from your source varchar is not an integer. You can post a set of sample data and expected result here to help answer your question.|||Source Columns Destination
Varchar Dec int
0660 10 66010
0660 2 66002
0660 3 66003
0660 11 66011|||

Kinny:

I put together a quick mock-up of what I think you are trying to do. Do either of the output columns look like what you are trying to do?

Dave

-- --
-- This mock-up works correctly for "correct values" as discribed in the request.
-- However, it does not know what to do with most "error conditions." If you don't
-- have to worry about "incorrect values" this select should work fine
--
-- Problem areas:
--
-- 1. What do you do with nulls?
-- 2. What do you do with 3-digit numbers?
-- 3. What do you do with negative numbers?
-- --
set nocount on
declare @.mockUp table
( sampleString varchar (20) not null,
sampleDecimal decimal (3,0) null
)

insert into @.mockUp values ('testView', null) -- ? Do you have to worry about null?
-- ? If so, display double-zero for nulls?
insert into @.mockUp values ('testView', 0) -- O0; OK
insert into @.mockUp values ('testView', 4) -- 04; OK
insert into @.mockUp values ('testView', 21) -- 21; OK
insert into @.mockUp values ('nextSample', 99) -- 99; OK
insert into @.mockUp values ('negative', -1) -- -1; Not sure; is this a problem?
insert into @.mockUp values ('negative', -99) -- 99; Wrong; is this a problem?
insert into @.mockUp values ('tooBig', 100) -- 00; Wrong; is this a problem?
--select * from @.mockUp

select sampleString,
sampleDecimal,
sampleString + isnull ( right('0'+convert(varchar(3), sampleDecimal), 2), '00')
as [String First],
isnull ( right('0'+convert(varchar(3), sampleDecimal), 2), '00') + sampleString
as [Decimal First]
from @.mockUp

-- --
-- Sample Output:
-- --

-- sampleString sampleDecimal String First Decimal First
-- -- - - -
-- testView NULL testView00 00testView
-- testView 0 testView00 00testView
-- testView 4 testView04 04testView
-- testView 21 testView21 21testView
-- nextSample 99 nextSample99 99nextSample
-- negative -1 negative-1 -1negative
-- negative -99 negative99 99negative
-- tooBig 100 tooBig00 00tooBig

|||

Hi,

Here is the update statement for question:

UPDATE yourTable

SET DesINTColumn= CAST(CAST(CONVERT(int, VarCharColumn1) AS varchar)+ RIGHT('0' + CAST(DecimalColumn1 AS varchar), 2) AS INT)

Converting a type (e.g. Decimal) BEFORE writing to ResultSet ?

As well known a DATE or TIMESTAMP type can be converted (to VARCAHR) after s
election but BEFORE
writing to the ResultSet by using the CONVERT function e.g.
SELECT CONVERT(char(10), MYTIMESTAMP, 101)) FROM ... WHERE ...
Is there something similar if the original field is a DECIMAL/NUMERIC field?
E.g.
SELECT DEC_CONVERT(char(30), MYDECIMAL, '###########0.00') FROM .... WHERE
...
GeorgeGeorge Dainis wrote:
> As well known a DATE or TIMESTAMP type can be converted (to VARCAHR)
> after selection but BEFORE writing to the ResultSet by using the
> CONVERT function e.g.
> SELECT CONVERT(char(10), MYTIMESTAMP, 101)) FROM ... WHERE ...
> Is there something similar if the original field is a DECIMAL/NUMERIC
> field?
> E.g.
> SELECT DEC_CONVERT(char(30), MYDECIMAL, '###########0.00') FROM ....
> WHERE ...
> George
Just use CONVERT or CAST:
Select CONVERT(char(30), MyDecimal) From ...
Select CAST(MyDecimal as char(30)) From ...
David Gugick
Imceda Software
www.imceda.com|||You can also use function STR.
Example:
declare @.d decimal(8, 2)
set @.d = 50.25 / 2.00
select @.d, str(@.d, 8, 2)
go
AMB
"George Dainis" wrote:

> As well known a DATE or TIMESTAMP type can be converted (to VARCAHR) after
selection but BEFORE
> writing to the ResultSet by using the CONVERT function e.g.
> SELECT CONVERT(char(10), MYTIMESTAMP, 101)) FROM ... WHERE ...
> Is there something similar if the original field is a DECIMAL/NUMERIC fiel
d?
> E.g.
> SELECT DEC_CONVERT(char(30), MYDECIMAL, '###########0.00') FROM .... WHER
E ...
> George
>|||>From the documentation ... "CONVERT converts a character string from
one character set to another. The datatype of the returned value is
VARCHAR2." So what you are seeing is an implicit conversion to varchar2
because you are using a function that accepts a char as input.
Look at the functions TO_CHAR(), TO_DATE(), TO_NUMBER() and CAST() for
type conversion ...
http://download-west.oracle.com/doc.../b10759/toc.htm|||Oh that was strange ... I accessed the question through
comp.databases.oracle.misc but google tells me that the answer will get
posted to a sqlserver group? hmmm.|||On 16 Feb 2005 04:51:03 -0800, David Aldridge wrote:

>Oh that was strange ... I accessed the question through
>comp.databases.oracle.misc but google tells me that the answer will get
>posted to a sqlserver group? hmmm.
Hi David,
The orinal question was crossposted to a total of three groups:
* comp.databases.oracle.misc
* microsoft.public.sqlserver.programming
* comp.databases.oracle
The followup-to was set to only the SQL Server group. The use of CONVERT
in the original question suggests that this is indeed a SQL Server related
question. I have no idea why the original poster has included two Oracle
groups in his crossposting.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"George Dainis" <george.dainis@.bluecorner.com> wrote in message
news:cuu58t$irh$04$1@.news.t-online.com...
> As well known a DATE or TIMESTAMP type can be converted (to VARCAHR) after
> selection but BEFORE
> writing to the ResultSet by using the CONVERT function e.g.
> SELECT CONVERT(char(10), MYTIMESTAMP, 101)) FROM ... WHERE ...
> Is there something similar if the original field is a DECIMAL/NUMERIC
> field?
> E.g.
> SELECT DEC_CONVERT(char(30), MYDECIMAL, '###########0.00') FROM ....
> WHERE ...
> George
>
Also well known are the TO_CHAR, ROUND, and TRUNC functions -- and don't
forget the CAST operator ;-)
++ mcs

Converting a decimal

I need to convert a value 5.300000000 to 0005300. Basically I want to remove the decimal point, pad the out put with a couple of zeros and return a value that is 7 bytes long. I may need to convert something like 10.300000000 to be 0010300.
Anyone have any thoughts?
Thanks.DECLARE @.x decimal(5,2)
SELECT @.x = 5.3
SELECT @.x, RIGHT(REPLICATE('0',7)+REPLACE(CONVERT(varchar(7), @.x * 10),'.',''),7)|||Hi,
Here is a little code that should do it for you.
Granted it is ugly but it works

declare @.d decimal (16,9)
set @.d = 9.3000

print Replicate('0', 7 - len ( replace( Left(@.d,Charindex('.',Cast(@.d as Varchar))+3),'.','')))+ replace(Left(@.d,Charindex('.',Cast(@.d as varchar))+3),'.','')

Hope that Helps

Tal McMahon|||I have a function for zero-padding, which cleans things up a bit in my queries:

CREATE FUNCTION [dbo].[fn_zero_pad]
(@.string_data VARCHAR(100),
@.new_length INT)
RETURNS VARCHAR(100) AS
BEGIN

RETURN REPLICATE('0', @.new_length - LEN(@.string_data)) + @.string_data

END

converting (casting) from decimal(24,4) to decimal(21,4) data type problem

Hello!

I would like to cast (convert) data type decimal(24,4) to

decimal(21,4). I could not do this using standard casting function

CAST(@.variable as decimal(21,4)) or CONVERT(decimal(21,4),@.variable)

because of the following error: "Arithmetic overflow error converting

numeric to data type numeric." Is that because of possible loss of the

value?

Thanks for giving me any advice,

Ziga

What was the value? For a value that fits in both, you should have no issue:

declare @.value decimal(24,4)
set @.value = 10.12

select cast(@.value as decimal(21,4))
go

But if the non-fractional value is too large for the 21,4 datatype, it will go boom:

declare @.value decimal(24,4)
set @.value = 12345678901234567890.1234

select @.value

select cast(@.value as decimal(21,4))

12345678901234567890.1234

Msg 8115, Level 16, State 8, Line 6
Arithmetic overflow error converting numeric to data type numeric.

If this isn't the case, then post the code that fails, the value, and the results of:

select @.@.version

I tried this on the following versions:

Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
Aug 26 2002 15:09:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Microsoft SQL Server 2005 - 9.00.2153.00 (Intel X86)
May 8 2006 22:41:28
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

|||Yes the non fraction value is to large... So there is no way to solve this problem?|||What is the result that you expect when you try to cast a larger value? CAST will throw error for overflow and that is the only expected behavior. If you want to fit larger values into smaller data type then you need to truncate the value yourself or perform other logic. You could use CASE expression to check for the larger values and conditionally perform cast. Also, take a look at ROUND function. You could use it instead of CAST for the larger values or for the whole conversion.|||

Thanks!

Actually I am performing some mappings between two systems. Interface for the destination system has specification of the field as decimal(21,4). In source system the (calculated) value is larger - decimal(24,4). For those large values where the cast (to decimal(21,4)) is not possible (arithemtic overflow) performing correct mapping is just not possible...

Thakns a lot,

Ziga

|||You still haven't answered how you would like to handle the larger values. Do you simply throw those away? What would it mean to store a truncated result in the database? And if you use it later then you are going to make wrong assumptions. It seems like your table schema is wrong and if you want to retain the higher precision values you need to modify the schema to match the source or vice versa. Otherwise, you will have to use round or truncate the value yourself before inserting and you cannot use CAST.

Convert/Cast from Varchar to decimal

guys, i've got a quick question on uploading a .txt or .csv file to a table
in sql server 2000.
i had been using a Bulk Insert to a dummy table where all columns are varcha
r.
then selecting that table and running it thru a for each loop in an asp.net
application and attempting the conversion there just using Cdbl(datarow.Item
(0))
or Cdec(datarow.Item(0)).
is there a better way to do this right on the database itself?
because it seems like somewhere an implicit rounding is occuring so i'll get
55.00 where 55.50 should be.
the problem i think is the inconsistant values, but, i thought i'd ask the r
eal experts.
as some of the values in the .csv (formerly .xls) file are 33.02, some are w
hole numbers 234 and even others are 55.5.
ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and the
consistant error i get is "Arithmentic overflow converting numeric to data t
ype numeric"
if anyone has any suggestions, i'd really appreciate it.
thanks again
rik
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Did you try altering that column in the dummy table to numeric(5, 2) instead
varchar?
AMB
"rik butcher" wrote:

> guys, i've got a quick question on uploading a .txt or .csv file to a tabl
e in sql server 2000.
> i had been using a Bulk Insert to a dummy table where all columns are varc
har.
> then selecting that table and running it thru a for each loop in an asp.ne
t
> application and attempting the conversion there just using Cdbl(datarow.It
em(0))
> or Cdec(datarow.Item(0)).
> is there a better way to do this right on the database itself?
> because it seems like somewhere an implicit rounding is occuring so i'll g
et 55.00 where 55.50 should be.
> the problem i think is the inconsistant values, but, i thought i'd ask the
real experts.
> as some of the values in the .csv (formerly .xls) file are 33.02, some ar
e whole numbers 234 and even others are 55.5.
> ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and th
e consistant error i get is "Arithmentic overflow converting numeric to data
type numeric"
> if anyone has any suggestions, i'd really appreciate it.
> thanks again
> rik
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NE
T resources...
>|||Why are all the columns of your dummy table all varchar? Assuming that
the data is clean, you can create the target table with a column of the
desired decimal type and have nothing else to do about conversion.
As far as the error you're seeing with CAST, it indicates that there is
a value in the [Amount] column that represents a value too big for
decimal(5,2), in other words, greater than 999.99 or less than -999.99.
Again assuming that the data is clean, CAST should work if the target
type can hold the values. You'll get a different error if you have non-
numeric strings, like 'abc':
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
I don't know the specifications of the Cdbl and Cdec functions, so I won't
comment on why some values seem to be changed more than rounding
should cause.
Steve Kass
Drew University
rbutch@.coair.com wrote:

>guys, i've got a quick question on uploading a .txt or .csv file to a table
in sql server 2000.
>i had been using a Bulk Insert to a dummy table where all columns are varch
ar.
>then selecting that table and running it thru a for each loop in an asp.net
>application and attempting the conversion there just using Cdbl(datarow.Ite
m(0))
>or Cdec(datarow.Item(0)).
>is there a better way to do this right on the database itself?
>because it seems like somewhere an implicit rounding is occuring so i'll ge
t 55.00 where 55.50 should be.
>the problem i think is the inconsistant values, but, i thought i'd ask the
real experts.
> as some of the values in the .csv (formerly .xls) file are 33.02, some are
whole numbers 234 and even others are 55.5.
>ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and the
consistant error i get is "Arithmentic overflow converting numeric to data
type numeric"
>if anyone has any suggestions, i'd really appreciate it.
>thanks again
>rik
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...
>|||thanks guys. you both were right. using numeric(5,2) worked but i still had
to widen that column again [that's what the overflow message was referring t
o] - so, i dont have to use a dummy table. and bcp bulk insert is working li
ke a charm.
i just had to look thru the actual values and there it was.
sometimes its the simplest things - and i appreciate you guys setting me str
aight on this.
thanks again
rik
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

Convert varchar to decimal

Hello, I am running a SELECT statement on a varchar 50 field. I would like
to convert the output to a numeric field with 2 decimal places. I tried a
CAST but that didn't work. Any suggestions would be appreciated. Thanks,
Pancho."Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work. Any suggestions would be appreciated. Thanks,
> Pancho.
Not knowing the largest decimal value you might wish to display, you could
do something like this:
SELECT CONVERT(<varchar field>, dec(10,2)) AS FieldName
FROM TableName
This will allow a total of 10 digits with 2 on the right of the decimal
point.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Pancho,
What didn't work?
Did you receive an error message?
If so, what?
If not, show us your code.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work. Any suggestions would be appreciated. Thanks,
> Pancho.|||Rick,
I tried CONVERT(FieldValue7, (dec(10,2)) AS NewFieldName
and got:
'dec' is not a recognized function name
Got the same error using 'decimal'. I am running a successful
SELECT CONVERT (CHAR (8), Field8, 112) AS Field8Text but the dec didn't work
.
"Rick Sawtell" wrote:

> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
>
> Not knowing the largest decimal value you might wish to display, you could
> do something like this:
> SELECT CONVERT(<varchar field>, dec(10,2)) AS FieldName
> FROM TableName
>
> This will allow a total of 10 digits with 2 on the right of the decimal
> point.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Hi Raymond,
I got the msg "Unable to convert varchar field" when using CAST. I am
taking data from a varchar and trying to format it to a numeric field, 10
wide with 2 decimal spaces. Pls see code above in my reply to Rick. Thanks
,
Pancho.
"Raymond D'Anjou" wrote:

> Hi Pancho,
> What didn't work?
> Did you receive an error message?
> If so, what?
> If not, show us your code.
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
>
>|||> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work.
What does "didn't work" mean? What did you try? Are you sure all of the
values are really numeric and can be converted? If so, why are you storing
them in a VARCHAR(50) column?|||> I tried CONVERT(FieldValue7, (dec(10,2)) AS NewFieldName
> and got:
> 'dec' is not a recognized function name
Did you try using the right syntax?
NewFieldName = CONVERT(DECIMAL(10,2), FieldValue7)|||> I got the msg "Unable to convert varchar field" when using CAST.
Well, because you decided to use a VARCHAR(50) to store decimals, invariably
you will get varchar data that is not a decimal.
You shouldn't be really surprised by this.
A start would be to filter out the rows where ISNUMERIC(column_name) = 0.
However, ISNUMERIC() is not perfect either, see http://www.aspfaq.com/2390
Then, try cleaning up your data and fixing the data type. When you do that,
you won't need to do a convert at all.|||You got me. I didn't design this DB; a vendor did. I would not have stored
a field which has cash values as a varchar either. I'm trying to convert it
from one vendor to another.
Thanks everyone for your comments. I'm closing this one now.
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "didn't work" mean? What did you try? Are you sure all of the
> values are really numeric and can be converted? If so, why are you storin
g
> them in a VARCHAR(50) column?
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OfuNFSyXGHA.4388@.TK2MSFTNGP03.phx.gbl...
> Did you try using the right syntax?
> NewFieldName = CONVERT(DECIMAL(10,2), FieldValue7)
>
Ooops.. Got it backwards. Thanks Aaron.
Rick

convert varchar to decimal

When running this query, several records are returned but then sql server gives the follwing error. Can you see how it can be solved please? Thanks

select
case
when isnumeric([Column 9]) = 1 then convert(decimal(24, 4), [Column 9])
else
null
end
from
tblCEMTradeFeed

Error:

Error converting data type varchar to numeric.

You can not trust function ISNUMERIC a 100%. This function returns 1 also for values like '.', '2E3' (scientific notation), '+', '-', but not all of them can be conevrted to numeric data type.

select cast('2E3' as float)

go

select cast('2E3' as numeric(5, 2))

go

What is wrong with IsNumeric()?

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

AMB

|||How do I fix the sql then please?|||

The following expression might fix a issue,

Note:

Is numeric function will return true for “.”, “$” & etc.

Code Snippet

case when

isnumeric([Column 9]) = 1

and patindex('%[0-9]%',[column 9]) <> 0

then convert(decimal(24, 4), [Column 9])

else

null

end

|||

Hi Manivannan.D.Sekaran,

I guess the OP is expecting a decimal separator in the data, if not, why to convert to numeric(24, 4).

I think that the expression:

> and patindex('%[0-9]%',[column 9]) <> 0

will not yield the expected result. The following value will cause an error '2E3'.

select

case

when isnumeric([Column 9]) = 1 and patindex('%[0-9]%',[Column 9]) <> 0 then convert(decimal(24, 4), [Column 9])

else null end

from

(select '2E3' as [Column 9]) as t

go

AMB

|||

Did you check the link I attached to the post?

AMB

|||

Hi HUNCHBACK (padern me i can't able to get your orginal name),

Yes I agree with you. I hope STR should be a right choice to convert the string to decimal

Code Snippet

select

[Column 9],

Case When

patindex('%[0-9]%',[Column 9])<>0

Then

Case When

Isnumeric(str(replace([Column 9],'$',''),24,4)) =1

Then Cast(str(replace([Column 9],'$',''),24,4) as Numeric(24,4))

End

End

from

(

select '2E3' as [Column 9]

Union All

select '2E24' as [Column 9]

Union All

select '2E80' as [Column 9]

Union All

select '100' as [Column 9]

Union All

select '.' as [Column 9]

Union All

select '$' as [Column 9]

Union All

select '$9' as [Column 9]

Union All

select '878.8373738' as [Column 9]

Union All

Select 'mani'

) as t

|||

Hi Manivannan.D.Sekaran,

Much better. Try adding:

(

...

union all

select '$9'

) as t

AMB

convert value to 2 digits after decimal

I'm having a query as follows:
SELECT 41.78 * 0.01 * 17 + 41.78 AS new_price
it returns 48.8826, but how do I set it up that it would round it nicely to two digits? as of 48.88
THANKS!In Oracle you would use ROUND(value,2).|||Just an observation, but rounding is really a client-side issue. In all but a few cases, it should really be handled by the client instead of the server.

Most servers will accept the:

SELECT Round(41.78 * 0.01 * 17 + 41.78, 2) AS new_price

syntax, but you still should do the rounding at the client if you can. Someday you might decide that you want the "full value", or maybe you'll need to round at a different precision than 2 for some clients... Not that I've ever had that happen, I just read about it in a book once. ;)

-PatP

convert value to 2 decimal places?

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.

Try this...

Code Snippet

select cast(columnName as numeric(10,2)) as 'columnName' from tableName

|||That worked, Thank You...

Convert String to Numeric without Decimal

Hi,

I am using Crystal Report 8.5 with Visual Basic 6.0 and MS Access database. Now situation is I have to display three different values for a particular condition. If data is there then number of records else zero and if even no depedend entry is there then "NA".

Variable is numeric and formula is

if {temp.var1} = 0 and {temp.var11} = 0 then 'NA'
else if {temp.var11} = 1 then '0'
else CStr ({temp.var1})

NOW problem is if I am using this report on a system having crystal report installed and set the numeric value as no decimal, it is working fine but when I try the same program on the system on which crystal is not installed it is showing decimal values upto 2 digits. I have used Report Expert Distribution also.

Any idea how to solve this problem. Thanks

VishalFormat that format field number as you want|||What does it mean. Could you please help me out by writing a example code for that.

thanks|||Roght click the field, select format section then goto number and select the required format

convert scientific notation to to decimal

I am importing a bunch of data which is all in varchar format which I cast into the relevant type on transfer to the production table. One of these columns contains decimals, but some of them are occasionally given in scientific notation (3.48E-02).
I am using

CAST (v1 AS decimal(18, 13))

to do the conversion, but that seems to be unable to handle the scientific notation. Does anyone know a way around this?Try converting to float first, like this:
CAST (CAST (v1 AS float(24)) AS decimal(18, 13))

Convert real => decimal (3,2)

Folks,
How can I convert a real value to a decimal value? As long as the real
value is not zero I am getting an arithmetic overflow error.
I tried both convert and cast.
Does anyone have an idea?
Cheers
StephanzHI Thiere
there is no need of type costing ...it is implecit type costing betweer
real and decimal numbers.
_
________________________________________
________
"Stephan Zaubzer" wrote:

> Folks,
> How can I convert a real value to a decimal value? As long as the real
> value is not zero I am getting an arithmetic overflow error.
> I tried both convert and cast.
> Does anyone have an idea?
> Cheers
> Stephanz
>|||Hmm - i can only duplicate if the real has more than 1 digit to the left
of the decimal... (implicit or explicit conversions)
e.g., 1.234 converts fine, but 12.34 does not
Stephan Zaubzer wrote:

> Folks,
> How can I convert a real value to a decimal value? As long as the real
> value is not zero I am getting an arithmetic overflow error.
> I tried both convert and cast.
> Does anyone have an idea?
> Cheers
> Stephanz|||On Thu, 22 Sep 2005 19:11:47 +0200, Stephan Zaubzer wrote:

>Folks,
>How can I convert a real value to a decimal value? As long as the real
>value is not zero I am getting an arithmetic overflow error.
>I tried both convert and cast.
>Does anyone have an idea?
Hi Stephan,
As Trey already indicated: you will get this error for data that is
above 9.99 or below -9.99. In the notation "decimal(3,2)", the first
number (3) is the TOTAL number of positions; the second number (2) is
the number if digits after the decimal point. That leaves only one digit
for the integer part.
If your values range from -999.99 to 999.99, use DECIMAN(5,2) instead.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

convert money datatype

Hello!

I need to convert money datatype into string without decimal points.

For example, I have column pmt_amt (money datatype)=27.00.

Is there a way to convert this amount into format "2700" without decimals?

Thanks,

Lena

Can you do that in the presentation layer?

declare @.m money

set @.m = 27

select @.m, str(@.m * 10000, 15, 0)

set @.m = 27.0015

select @.m, str(@.m * 10000, 15, 0)

AMB

|||

Thank you!

I tried your conversion and here's my results:

value in table converted

47.02 470200 1202.78 12027800 411.91 4119100 20401.78 204017800 711.01 7110100 517.83 5178300 11756 117560000 773 7730000 3439.75 34397500 4062.66 40626600

How can I get rid of zeroes in the end of converted values?

E. G.: for 40626600 I need it t be 406266.

|||

I would 'refine' AMB's suggestion with the following:

If you want the results to have ONLY 2 numbers to the right of the decimal, then use 100 instead of 10000 in the str() function. That is because the money datatype has four (4) numbers to the right of the decimal, and EVEN though you often ignore them, they are there.

(It will still round up/down as needed.)

And if you have really large numbers, the '15' in the string function may need to be increased.

|||

Money data type has 4 decimal digits. If you want to consider just the first two, then multiply the value by 100 instead 10000.

declare @.m money

set @.m = 4062.66

select @.m, str(@.m * 100, 15, 0)

AMB

|||

Thank you for all your replies.

It helped a lot.

convert int to money without decimal and cents

I am doing the following to change an int into money (I want the commas) but it is adding a decimal and 2 zeroes at the end:
convert(varchar,convert(money,m.[FirstTier]),1) as 'First Tier',

m.firsttier = '1583456'
after conversion = '1,583,456.00'

Is there any easy way to not have the '.00'?

Thanks.this trick should do:
parsename(convert(varchar,convert(money,m.[FirstTier]),1),2) as 'First Tier'|||That worked... thanks!
|||Note that PARSENAME returns unicode string so you need to be careful when using that expression in WHERE clause for example. (for example if the column against which you are comparing is indexed and it is varchar , then with this expression the column will be converted to unicode) On the other hand, if this is for display purposes then it is better done in the client-side. Doing it on the client-side, you can handle other regional settings also.

Convert HH,MM to decimal ?

I have a table with a specific column that i get from an AS/400
The column holds worktime specified in HH,MM format. How do i convert
that do a decimal number.
ie. 7,45 (7 Hours and 45 minutes) i want it to become 7,75.
Thankskjo007@.hotmail.com wrote:

> ie. 7,45 (7 Hours and 45 minutes) i want it to become 7,75.
select datepart(hour, '2005-01-01 18:45') + cast(datepart(minute,
'2005-01-01 07:45 AM') as decimal) / 60
HTH,
Stijn Verrept.|||>> ie. 7,45 (7 Hours and 45 minutes) i want it to become 7,75.
>
> select datepart(hour, '2005-01-01 18:45') + cast(datepart(minute,
> '2005-01-01 07:45 AM') as decimal) / 60
I don't think that will work as "HH,MM" is not a recognised Sql date format
and therefore the date functions you use will not return the expected data..

> HTH,
> Stijn Verrept.
>
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||Peter wrote:

> I don't think that will work as "HH,MM" is not a recognised Sql date
> format and therefore the date functions you use will not return the
> expected data..
? What do you mean? I tried it out, it works without problems. He
wants the time converted to decimal and that's what this does:
select datepart(hour, '2005-01-01 07:45 AM') + cast(datepart(minute,
'2005-01-01 07:45 AM') as decimal) / 60
Kind regards,
Stijn Verrept.|||
> ? What do you mean? I tried it out, it works without problems. He
> wants the time converted to decimal and that's what this does:
> select datepart(hour, '2005-01-01 07:45 AM') + cast(datepart(minute,
> '2005-01-01 07:45 AM') as decimal) / 60
> --
> Kind regards,
> Stijn Verrept.
>
He said he is receiving the data from AS/400 in the format "HH,MM" and gave
the example "7,45"
Substituting that into your solution gives...
select datepart(hour, '7,45') + cast(datepart(minute, '7,45') as decimal) /
60
... which doesn't work!
Regards
Peter
--== Posted via mcse.ms - Unlimited-Unrestricted-Secure Usenet News=
=--
http://www.mcse.ms The #1 Newsgroup Service in the World! 120,000+ New
sgroups
--= East and West-Coast Server Farms - Total Privacy via Encryption =--|||<kjo007@.hotmail.com> wrote in message
news:1139314990.653609.216860@.f14g2000cwb.googlegroups.com...
>I have a table with a specific column that i get from an AS/400
> The column holds worktime specified in HH,MM format. How do i convert
> that do a decimal number.
> ie. 7,45 (7 Hours and 45 minutes) i want it to become 7,75.
> Thanks
declare @.time varchar(5)
set @.time = '7,45'
select cast(left(@.time, charindex(',', @.time)-1) + (right(@.time,
len(@.time) - charindex(',', @.time)))/60.0 as decimal(5,2))|||It may be worth creating a function for this logic if it is needed in more
than one place.
usd_ConvertAS400Time(7,45) will be easier to type, read, and maintain.
However, there may be a performance hit, I'm not really sure.
Then again, changing the column and your AS400 import to store the value
differently may remove more headaches, depending on if you ever use the data
in the current format.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23ZoNr3$KGHA.2276@.TK2MSFTNGP15.phx.gbl...
> <kjo007@.hotmail.com> wrote in message
> news:1139314990.653609.216860@.f14g2000cwb.googlegroups.com...
> declare @.time varchar(5)
> set @.time = '7,45'
> select cast(left(@.time, charindex(',', @.time)-1) + (right(@.time,
> len(@.time) - charindex(',', @.time)))/60.0 as decimal(5,2))
>|||Peter wrote:

> He said he is receiving the data from AS/400 in the format "HH,MM"
> and gave the example "7,45"
> Substituting that into your solution gives...
> select datepart(hour, '7,45') + cast(datepart(minute, '7,45') as
> decimal) / 60
> ... which doesn't work!
Very true, my bad!
HTH,
Stijn Verrept.|||Of course, either way there could be a performance hit.
If he cannot change the table structure...
it may be better (easier/faster) for the poster to do the conversion client
side..
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:ejySWSALGHA.532@.TK2MSFTNGP15.phx.gbl...
> It may be worth creating a function for this logic if it is needed in more
> than one place.
> usd_ConvertAS400Time(7,45) will be easier to type, read, and maintain.
> However, there may be a performance hit, I'm not really sure.
> Then again, changing the column and your AS400 import to store the value
> differently may remove more headaches, depending on if you ever use the
> data
> in the current format.
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:%23ZoNr3$KGHA.2276@.TK2MSFTNGP15.phx.gbl...
>|||maybe this:
select replace('7,45', ',', ':')
7:45

convert from hexadecimal to a decimal

We have a tableA with a varchar field whose contents are
hexadecimal. We want to insert these hexadecimal contents
from a varchar field into different table, tableB in
decimal format.
We tried to use Cast and convert functions to explicitly
convert into int field thinking it will give us right
decimal, which didn't work.
Example of what works:
Select CAST(Cast(cast(0x2A as varchar) AS varbinary) AS
int) returns value of 42.
This doesn't work:
Select CAST(Cast(cast('0x2A' as varchar) AS varbinary) AS
int)
All the values coming from tableA are in '0x2A' form
because the field is defined as a varchar. We can set the
field name in tableB to whatever we want (int, decimal or
even varbinary)
Could someone please pointers / suggestions on how to
convert hexadecimal to int or even how to remove the
leading and trailing ' ?
Thanks in advance
Skip,
This is not too efficient, but it should do the trick. It doesn't
validate the input at all, either.
create function hexchar(
@.b varchar(10)
) returns int
as begin
declare @.n bigint
set @.n = 0
declare @.digits char(16)
set @.digits = '0123456789ABCDEF'
set @.b = substring(@.b,3,8)
while len(@.b) > 0 begin
set @.n = 16*@.n + charindex(substring(@.b,1,1),@.digits)-1
set @.b = substring(@.b,2,8)
end
return
case when @.n >= 0X80000000
then @.n - 0x0100000000
else @.n end
end
go
-- Steve Kass
-- Drew University
-- Ref: 8EB8CE54-6E8E-47C1-93AB-35AB3F7C27F5
Skip wrote:

>We have a tableA with a varchar field whose contents are
>hexadecimal. We want to insert these hexadecimal contents
>from a varchar field into different table, tableB in
>decimal format.
>We tried to use Cast and convert functions to explicitly
>convert into int field thinking it will give us right
>decimal, which didn't work.
>
>Example of what works:
>Select CAST(Cast(cast(0x2A as varchar) AS varbinary) AS
>int) returns value of 42.
>This doesn't work:
>Select CAST(Cast(cast('0x2A' as varchar) AS varbinary) AS
>int)
>All the values coming from tableA are in '0x2A' form
>because the field is defined as a varchar. We can set the
>field name in tableB to whatever we want (int, decimal or
>even varbinary)
>Could someone please pointers / suggestions on how to
>convert hexadecimal to int or even how to remove the
>leading and trailing ' ?
>Thanks in advance
>
>
|||On Wed, 29 Sep 2004 00:27:03 -0400, Steve Kass wrote:

>This is not too efficient, but it should do the trick.
Hi Steve,
How about using a numbers table to speed it up?
create function hexchar2(
@.b varchar(10)
) returns int
as begin
declare @.n bigint
set @.b = substring(@.b,3,8)
set @.n = (select
sum((charindex(left(right(@.b,n),1),'0123456789ABCD EF')-1)*POWER(16,(n-1)))
from dbo.numbers
where n between 1 and len(@.b))
return
case when @.n >= 0X80000000
then @.n - 0x0100000000
else @.n end
end
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Very good, and how about this?
create function hexchar2(
@.b varchar(10)
) returns int
as begin
return (
select
sum((charindex(right(left(@.b,N),1),'123456789ABCDE F'))*POWER(16.,len(@.b)-N))
from numbers
where n between 3 and len(@.b))
- case when lower(@.b) like '0x[89abcdef]'+replicate('[0-9abcdef]',7)
then 0x0100000000 else cast(0 as bigint) end
end
SK
Hugo Kornelis wrote:

>On Wed, 29 Sep 2004 00:27:03 -0400, Steve Kass wrote:
>
>
>Hi Steve,
>How about using a numbers table to speed it up?
>create function hexchar2(
> @.b varchar(10)
>) returns int
>as begin
> declare @.n bigint
> set @.b = substring(@.b,3,8)
> set @.n = (select
>sum((charindex(left(right(@.b,n),1),'0123456789ABC DEF')-1)*POWER(16,(n-1)))
> from dbo.numbers
> where n between 1 and len(@.b))
> return
> case when @.n >= 0X80000000
> then @.n - 0x0100000000
> else @.n end
>end
>go
>
>Best, Hugo
>
|||On Fri, 01 Oct 2004 03:35:26 -0400, Steve Kass wrote:

>Very good, and how about this?
>create function hexchar2(
> @.b varchar(10)
>) returns int
>as begin
> return (
> select
>sum((charindex(right(left(@.b,N),1),'123456789ABCD EF'))*POWER(16.,len(@.b)-N))
> from numbers
> where n between 3 and len(@.b))
> - case when lower(@.b) like '0x[89abcdef]'+replicate('[0-9abcdef]',7)
> then 0x0100000000 else cast(0 as bigint) end
>end
>SK
Hi Steve,
Nice!
With the added advantage that it can be used inline in the query, so that
the overhad of calling a function is no longer incurred. (Though I would
comment it if I used it in a query <g>)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||You mean this isn't self-documenting? ;)
select
'0x[89abcdef]' lower, '[0-9abcdef]' upper,
0x100000000 parsename, '123456789ABCDEF' power,
cast(0 as bigint) replicate, 16. stuff,
7 charindex, 3 rtrim
into ltrim
select
b, sum(charindex(right(left(b,N),1),power)
* power(stuff,len(b)-N))
- case when lower(b) like lower+replicate(upper,charindex)
then parsename else replicate end
from T, numbers, ltrim
where n between rtrim and len(b)
group by b, lower, upper, parsename, replicate, power, charindex
go
SK
Hugo Kornelis wrote:

>On Fri, 01 Oct 2004 03:35:26 -0400, Steve Kass wrote:
>
>
>Hi Steve,
>Nice!
>With the added advantage that it can be used inline in the query, so that
>the overhad of calling a function is no longer incurred. (Though I would
>comment it if I used it in a query <g>)
>Best, Hugo
>
|||On Fri, 01 Oct 2004 18:10:12 -0400, Steve Kass wrote:

>You mean this isn't self-documenting? ;)
<snort>
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi all,
when i run this query, it will result this :
Server: Msg 207, Level 16, State 3, Procedure hexchar2, Line 5
Invalid column name 'n'.
May i know the 'n' factor is ?

Quote:

Originally posted by Hugo Kornelis
On Wed, 29 Sep 2004 00:27:03 -0400, Steve Kass wrote:

>This is not too efficient, but it should do the trick.
Hi Steve,
How about using a numbers table to speed it up?
create function hexchar2(
@.b varchar(10)
) returns int
as begin
declare @.n bigint
set @.b = substring(@.b,3,8)
set @.n = (select
sum((charindex(left(right(@.b,n),1),'0123456789ABCD EF')-1)*POWER(16,(n-1)))
from dbo.numbers
where n between 1 and len(@.b))
return
case when @.n >= 0X80000000
then @.n - 0x0100000000
else @.n end
end
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

|||On Wed, 27 Oct 2004 19:31:12 -0500, landung wrote:

>hi all,
>when i run this query, it will result this :
>Server: Msg 207, Level 16, State 3, Procedure hexchar2, Line 5
>Invalid column name 'n'.
>May i know the 'n' factor is ?
Hi landung,
It's a column in the numbers table I used for this query.
If you don't have a numbers table yet, check out this link:
http://www.aspfaq.com/show.asp?id=2516
If you do have a numbers table, but with another column name, change the
query to reflect the names of your numbers table and column.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
Don't forget that if you want something that can be put inline without
a UDF call, you can use something more like this:
create function hexchar2(
@.b varchar(10)
) returns int
as begin
return (
select
sum((charindex(right(left(@.b,N),1),'123456789ABCDE F'))*POWER(16.,len(@.b)-N))
from numbers
where n between 3 and len(@.b))
- case when lower(@.b) like '0x[89abcdef]'+replicate('[0-9abcdef]',7)
then 0x0100000000 else cast(0 as bigint) end
end
SK
Hugo Kornelis wrote:

>On Wed, 27 Oct 2004 19:31:12 -0500, landung wrote:
>
>
>Hi landung,
>It's a column in the numbers table I used for this query.
>If you don't have a numbers table yet, check out this link:
>http://www.aspfaq.com/show.asp?id=2516
>If you do have a numbers table, but with another column name, change the
>query to reflect the names of your numbers table and column.
>Best, Hugo
>