Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Another help in converting data in a query 1

Status
Not open for further replies.
May 17, 2006
54
US
I have a numeric data field called amount(numeric 28,12), data looks like this:

281.400000000000
17892.840000000000
.170000000000

I need to format in a query to look like this:

000000000281.40
000000017892.84
000000000000.17

Thanks ...


 
Are you sure you want this?
What if in yor field you have:
17892.841231233577
instead of
17892.840000000000
Then what?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
and update (I should save this somewhere and then just use Copy & Paste)

FORMATTING DATA IS YOUR FRONTEND JOB, NOT THE SQL SERVER ONE!!!!!


:)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Look out. Now he's mad.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
[rofl]

I am :)
Why everybody tries to do something that S/He could do EASIER in every front end imagine job for SQL Server (If the fron end didn't then change it). Then send all of this through network?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Thanks for the lecture, but I am trying to write a query that creates a flat file so I can move some daily transactional data from one system a different system.

The originating data is always 2 decimals.
 
Try....

Code:
Select	Right(Replicate('0', 12) + Convert(VarChar(30), Convert(Decimal(28,2), Amount)), 15)
From	YourTable

Basically, convert to decimal(28,2) to get rid of extra precision, then convert to varchar. Pad 0's in front (the replicate part) and then take the right most values.

[blue]** This will [!]NOT[/!] work for negative numbers.[/blue]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
OK then :)
use the field as it is.Do not covert it to something fancy with leading zeroes and split the decimals. The other system MUST get it and convert it to its own needs.
BTW you didn't answer to my first question.

If the example you show us is the result you want:
Code:
DECLARE @test numeric(28,12)
SET @test = 17892.84
SELECT @test AS Unformated,
       ROUND(@test,2) AS Rounded,
       RIGHT(REPLICATE('0',15)+CAST(CAST(ROUND(@test,2) as numeric(15,2)) as varchar(15)), 15) AS Formated
But as I ask what IF you have:
17892.841231233577 in that field?
What if you have
12345678901234.6789012345678
Then what?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Thanks George, thats works perfectly. While some of my numbers are negative (I am bringing over journal entries), the next space on my import file contains a blank or negative sign indicating such and I have that part figured out. Thanks ...
 
What IF?

You didn't have such values, YET! :)
But you will, if you design the field with such length and precision.

But that is YOUR application and YOUR choice :)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top