How do I convert an integer to a decimal? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How do I convert an integer to a decimal?

Author

Topic

tomcatuk
Starting Member

6 Posts

Posted - 2009-11-03 : 04:49:21

I've got two tables, both storing prices. The data comes from external sources for each table, and the prices are not the same (I need them to be as I want to compare them...or do I?).

First Table the prices are integers ie 100.00 is 100000 in the database. This field is set as type "FLOAT".

Second table they are decimals ie 100.00 IS 100.00. This field is set as type "VARCHAR" with lenght/Values set at 32.

Based on the fact I want to create a view by comparing and finding the lowest price from the two tables for a given record, do I need to alter one of these fields to match the format of the other, or will I be able to make the values comparable through the view itself? If it's the former I'd assume I would want to change the field in the first table - how would I go about that baring in mind the data will get re-imported from it''s external source?

Andy Fletcher - just another blog.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-03 : 06:12:14

quote:
100.00 is 100000

unbelievable

quote:
the prices are integers ... This field is set as type "FLOAT".

unbelievable

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-03 : 10:47:41

you can convert em in your query, but i'd have a hard look at altering the table definitions to store the data with proper types

tomcatuk
Starting Member

6 Posts

Posted - 2009-11-03 : 13:12:06

Thanks Russel - can you point me in any direction on how to go about it?

Andy Fletcher - just another blog.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-03 : 13:18:15

sure. please show the ddl for the tables

tomcatuk
Starting Member

6 Posts

Posted - 2009-11-05 : 14:51:02

Thanks again Russel - I didn't really get what you said, and not wishing to appear TOO stupid I ran off and found out what you were asking. During that process I worked out what I needed to do.

Andy Fletcher - just another blog.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-05 : 15:02:34

something like this?


DECLARE @x float, @y varchar(32), @x1 money, @y1 money

SELECT @x = 100000, @y = '100.00'
SELECT @x1=CONVERT(money,@x/1000),@y1=CONVERT(money,@y)

IF @x1 = @y1
	PRINT 'Equal'
  ELSE
	PRINT 'NOT EQUAL'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-05 : 20:47:01

Sweet. Glad you got it resolved

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources