0

I am writing an export for a customer and they are requesting that all fields have leading 0's up to 10 digits. I have done that on all fields except for two fields. I am using this query:

SELECT RIGHT('0000000000'+ISNULL(RTRIM(a.tqtyshp),''),10)as tqtyshp, RIGHT('0000000000'+ISNULL(RTRIM(a.price),''),11) AS price, 
FROM artran a
LEFT JOIN arcust b ON a.custno = b.custno
WHERE invdte BETWEEN DATEADD("d",-7,GETDATE()) and GETDATE()+5 AND code = '127'

And I get the following results:

003.000000  00034.64000
003.000000  00029.96000
003.000000  00032.65000
003.000000  00031.58000
003.000000  00022.18000

I need the results to look like this:

00000003.00 00000034.64
00000003.00 00000029.96
00000003.00 00000032.65
00000003.00 00000031.58
00000003.00 00000022.18

The fields in question are formatted as decimal(15,6)

This is similar to the question I asked yesterday but in this question I am trying to format the decimal places. The other question I was just trying to get the total character count to 10.

IowaMatt
  • 57
  • 1
  • 3
  • 10
  • `replicate('0', 11 - len(youField)) + cast (yourField as varchar) as FieldName` – xQbert May 24 '17 at 14:59
  • 3
    I don't think this question is the duplicate of the other question. This question is completely different from the other one. – Crazy Cucumber May 24 '17 at 15:00
  • This is not a duplicate. A better answer for this question is to format in SSRS, not in SQL. – Jamie F May 24 '17 at 15:05
  • I tried to edit in SSRS. The only problem is that when I export as a tab delimited file it removes the leading 0's – IowaMatt May 24 '17 at 15:19
  • @IowaMatt When you open the tab file in a text editor they aren't there or when you open it with Excel (I suspect Excel is recognizing the numbers and removing the leading zeros.) If these are strings, which they need to be to display in SSRS with leading zeros, they should get exported with the leading zeros. – Jamie F May 24 '17 at 15:50
  • @Jamie F - I am opening it in text editor(tried notepad and notepad++) – IowaMatt May 24 '17 at 16:00

1 Answers1

0

How about 

SELECT
    RIGHT('0000000000' + cast(a.tqtysh AS varchar), 10) as tqtyshp,
    RIGHT('0000000000'+ISNULL(CAST(a.price AS varchar),''),11) AS price, 
FROM 
    artran a
LEFT JOIN arcust b ON a.custno = b.custno
WHERE
    invdte BETWEEN DATEADD("d",-7,GETDATE()) and GETDATE()+5 
 AND code = '127'
Jonny
  • 1,037
  • 7
  • 15