format currency as flat fixed value

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

format currency as flat fixed value

Author

Topic

tigerweld
Starting Member

12 Posts

Posted - 2011-05-26 : 12:00:14

I'm trying to run a query that has a currency field. I prefer to have it formatted as a flat fixed width field. Currently it comes out like

232.5
25
166.5

On vaules that don't have anything to the right of the decimal it leaves the decimal off. I need to format like the following:

0000023250
0000002500
0000016650

Can someone show me how to do this?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-26 : 12:14:41

This should be done in the front-end app, but
select RIGHT('0000000000'+ convert(varchar(10),convert(int,CurrencyField*100)),10)

Jim

Everyday I learn something that somebody else already knew

tigerweld
Starting Member

12 Posts

Posted - 2011-05-26 : 12:27:12

Not sure I follow you. Here is my query.

SELECT Client_db.dbo.Deposits.DDA,
Client_db.dbo.Deposits.SiteID,
Client_db.dbo.Deposits.TimeTransmitted,
Client_db.dbo.MICR.CheckNumber,
Client_db.dbo.MICR.Amount

FROM Client_db.dbo.Deposits
INNER JOIN Client_db.dbo.MICR
ON Client_db.dbo.MICR.DepositID = Client_db.dbo.Deposits.DepositID
WHERE Client_db.dbo.Deposits.CustomerID = '132'

The 'Amount' field is the one in question.

tigerweld
Starting Member

12 Posts

Posted - 2011-05-26 : 12:30:02

Nevermind, I got it. I replaced CurrencyField with my field name, sorry!

tigerweld
Starting Member

12 Posts

Posted - 2011-05-26 : 12:32:06

I have one more format question regarding the TimeTransmitted field. Do I post it here or start a new thread for others?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-26 : 13:03:20

Start a new thread.

Jim

Everyday I learn something that somebody else already knew

tigerweld
Starting Member

12 Posts

Posted - 2011-05-26 : 14:20:22

Thanks Jim! I GREATLY appreciate it!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-29 : 18:56:28

>> I'm trying to run a query that has a currency field. <<

Columns are not fields; currency is not a data type. That was COBOL in the 1950's.

>> I prefer to have it formatted as a flat fixed width field [sic]. <<

Good SQL programmers do the display formatting in the front end, never in the database. That was also COBOL. All you got was kludges to make you a worse programmer. Sorry.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources