Why are leading zeros not displaying?

[Jessica Tubbs]

Hi,
I am trying to add leading zeros to a field but the zeros are not displying in the results.

The code below will display the added zeros because I have a space at the beginning of my leading zeros:

SELECT ' 0000000'+ STR(DATE_FIELD,7,0) as DATEFMT
FROM tbl_1

Results: ' 0000000999999'

Note: DATE_FIELD is a float in tbl_1

But when I add the RIGHT function to cut the field down to 7 characters I get the following:

SELECT RIGHT(' 0000000'+ STR(DATE_FIELD,7,0),7) as DATEFMT
FROM tbl_1

Displayed Results: '999999'

I want to see this displayed as '0999999'

I've tried casting the whole RIGHT function to a char(7) but that doesn't work.

Can someone please help tell me how I can get the zeros to display?

[ck9663]

Use cast instead of str()

right('0000000' + cast(date_field as varchar(7)),7)

Happy Coding!!!

~~ CK

[Jessica Tubbs]

Hi ck9663

I tried your solution but get the arithmetic overflow error for type varchar. Any other suggestions?

[Jessica Tubbs]

Hi ck9663

I tried your solution but get the arithmetic overflow error for type varchar. Any other suggestions?