How to get to those money $$? - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

How to get to those money $$?

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-12-06 : 12:38:06

I need to select a set of data including a field called AMT.
The underlying data is of type float 8.
A sample of them look like this:

182.86999999999998
13.10
322.27999999999995

The end result shoud look like this:
0000000018287
0000000001710
0000000032228

Here is the crete table sql

use northwind

CREATE TABLE #TempOrders (
[OrderID] [int] NOT NULL ,
[AMT] [float] NOT NULL
) ON [PRIMARY]
GO
insert into #TempOrders values (1, 182.86999999999997)
insert into #TempOrders values (2, 13.10)
insert into #TempOrders values (3, 322.27999999999995)

Thank$!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-06 : 13:03:13

select right('0000000000000' + convert(varchar(13),convert(int,convert(decimal(18,2),amt)*100)),13)
or
select right('0000000000000' + replace(str(amt, 14, 2), '.',''), 13)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-12-06 : 13:28:06

Thank you very much.
It works!
Right on the 'money'!

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-12-06 : 14:18:10

Oh, one more thing, how do I right justify the result?
Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-06 : 16:15:18

the
right('0000000000000' + @s, 13)
should right justfy it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-06 : 18:54:00

presentation !!!! not a job for T-SQL. Don't write reports in Query Analyzer.

(why do people make things so hard on themselves???)

- Jeff

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-12-07 : 12:24:57

Actually this is a case of EDI. The rigid formating requirement is not for human users but for another machine. The bank's IBM mainframe dictates. So it becomes part of my job.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources