Number format

Question

Post reply

Number format

Larissa-136281

SSC Veteran

Points: 292
More actions
November 29, 2006 at 10:58 pm

#117825

I am extracting data into text file using dts. Column format is decimal(14,2). All "0.00" values are extracted as ".00"
In Query Analyser results also shown as ".00"
select 0.00
returns ".00"
Is there any way to keep 0 before decimal point in output? Configuration settings?

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

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

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 1

SELECT STR(0.00,10,2)

SELECT STR(.00,10,2)

SELECT STR(0,10,2)

--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)

Vladan SSC-Insane Points: 22072 More actions · Answer 2

Yes, you have to convert the number to character data type.

Depending on the type of output you may find it useful to add LTRIM to the conversion suggested by Jeff, to get rid of blank spaces (e.g. for delimited output) - or leave it as it is, if output is fixed length.

Larissa-136281 SSC Veteran Points: 292 More actions · Answer 3

I was looking for the way to keep it as number. Result go to dts and I need manually set it to not quotable and append spaces for alignmnet. You suggestions seems the way to do it

Thanks

Just to finish this topic. For some reason SQL formats numeric and float differently, float returned with leading 0, decimal and money not. At least with my server configuration. I did not find any server/db level settinsg to control it. Here are the samples

select 'money, with varchar cast', cast(cast (0.00 as money) as varchar(10))

select 'money, no varchar cast', cast(0.00 as money)

select 'decimal(10,2) with varchar cast', cast(cast (0.00 as decimal(10,2)) as varchar(10))

select 'decimal, no varchar cast', cast(0.00 as decimal(10,2))

select 'float, with varchar cast', cast(cast (0.00 as float) as varchar(10))

select 'float, no varchar cast', cast(0.00 as float)

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 4

Why do you need DTS to preserve the leading 0? What are you using DTS for? Are you trying to create an "output file" of some sort? Why do you think it must be in a "numeric format"?

--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)

Larissa-136281 SSC Veteran Points: 292 More actions · Answer 5

this is client's requrement, not mine. He wants daily extracts as txt file, tab delimited, numbers as 0.00, not qoutable, right justified. In DTS, if you use default transformation it sets strings as quotable. I used str(num, 12,2) in sp, then modified destination to non-quatbale. I got result they need, DTS works and schdiuled to run.

Thansk everybody for help