Dropping zeros

Question

Post reply

Dropping zeros

Mandy Edwards

Newbie

Points: 2
More actions
March 27, 2007 at 9:18 am

#172602

I am using SQL select statements to pull data into letters. When I pull an amount of money which has a zero on the end eg 12.90 it drops the zero on the end which makes it look weird in the letter.
How can I format it to make sure there are 2 numbers after the dp?
My SQL looks like
select pva_number_value DD_INSTALL_AMOUNT
where DD_INSTALL_AMOUNT is the name of the field I am pulling the number from
Sorry if this is really basic because I am totally new to this and trying to amend someone elses SQL to do what I want
Mandy

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

Try

select convert(decimal(9, 2), DD_INSTALL_AMOUNT)

Russel Loski, MCSE Business Intelligence, Data Platform

EdVassie SSC Guru Points: 60531 More actions · Answer 2

You should treat SQL as a means of obtaining data, and treat the VB (or other) application code as a means to format it as you require.

Although you can use Cast() in SQL to force trailing 0s in a decimal field, it will use a lot more CPU and time in SQL than doing the same process in VB.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

Agreed... WHEN you have an application... and if you save the data into a table with the correct decimal data type, takes no time at all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mandy Edwards Newbie Points: 2 More actions · Answer 4

Thanks, all I'll have a play around and see what I can achieve, at least you have helped point me in the right direction

Mandy