If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Convert text to float in SQL Statement

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 4
Question Convert text to float in SQL Statement

Hi,

Can you guys help me out?
I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast".

I do get the answer but its not the correct figure. My SQL statement is as follow:

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName

Please help.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: Epsom, United Kingdom
Posts: 42
Re: Convert text to float in SQL Statement

Try avoiding using flaot, pretty inaccurate.

Use decimal or numeric data types instead.
__________________
Shadow to Light
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 4
Re: Convert text to float in SQL Statement

Quote:
Originally posted by Crespo-n00b
Try avoiding using flaot, pretty inaccurate.

Use decimal or numeric data types instead.
I've tried it and it still gives me a wrong answer.
I should have 45299 + 7832.5 = 53131.5, but I kept getting
13310.

Any more ideas?
Reply With Quote
  #4 (permalink)  
Old
The SQL Apostle
 
Join Date: Jul 2003
Location: The Dark Planet
Posts: 1,397
A Miracle ????
Code:
use pubs

create table answerint
(
QTY1 varchar(20),
QTY2 varchar(20)
)

insert into answerint select '45299','7832.5'

SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM answerint

drop table answerint
Works for me though !!!
__________________
Get yourself a copy of the The Holy Book

order has no physical Brett in The meaning of a Kaiser . -database data
Reply With Quote
  #5 (permalink)  
Old
Window Washer
 
Join Date: Nov 2002
Location: Jersey
Posts: 10,322
Crespo and a mriacle in the same post...holy cow!

Where have you been hiding out?

And Yes float is quirky....

But I would add

Quote:
USE Northwind

CREATE TABLE answerint
(
QTY1 varchar(20),
QTY2 varchar(20)
)

INSERT INTO answerint
SELECT '45299','7832.5' UNION ALL
SELECT 'BRETT', 'KAISER'

SELECT SUM(
(Cast(Qty1 as float))
+ (Cast(Qty2 as float))
) as intAnswer FROM answerint
WHERE ISNUMERIC(Qty1) = 1 AND ISNUMERIC(Qty2) = 1

DROP TABLE answerint
__________________
Brett
8-)

It's a Great Day for America everybody!

dbforums Yak CorralRadio 'Rita
dbForums Member List
I'm Good Once as I ever was

The physical order of data in a database has no meaning.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: Epsom, United Kingdom
Posts: 42
Brett,

I have not been hiding anywhere. Just busy with work and life you know....

Amethystium.
__________________
Shadow to Light
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 4
Quote:
Originally posted by Crespo-n00b
Brett,

I have not been hiding anywhere. Just busy with work and life you know....

Amethystium.
Thanks for the replies.
I've tried it all out and they work for single select but not when a
"SUM" is used.

But I've found out that the answer came out the way it was because there were some NULL values in QTY2. When this happened, the result would be NULL even if QTY1 contained a figure.

Anymore ideas? I'm planning to manually grab these out evaluate/convert them using ASP before doing a calculation.

Thanks
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On