SQL Server 7.0 ANSI_Defaults Off Issues

[kimr]

I am having trouble getting a char(7) that is converted to an integer to pad with zeroes in SQL Server 7.0. The statement is as follows:
convert(char(7),RIGHT('0000000'+convert(char(7),convert(integer,isnull(tblname.column*100,0))),7))

This works correctly on 6.5, but when I test on 7.0 the numbers come out as either 0 or a number, neither padded.

This is a stored procedure that sets ansi_defaults off and I believe this is the problem.

Any suggestions?

 

[tlbroadbent]

You need a slight change due to the way padding is handled. Convert the 2nd char(7) to varchar(7). Then you quuery should work.

convert(char(7),RIGHT('0000000'+convert(varchar(7),convert(integer,isnull(tblname.column*100,0))),7))

You might want to consider using other functions to sikmplify the conversion. At least, I think it is simpler. ;-)

cast(replace(str(isnull(tblname.column*100,0),7,0),' ','0') As char(7)) Terry

http://members.home.net/tlbroadbent/prog.htm

_____________________________________
Man's mind stretched to a new idea never goes back to its original dimensions. - Oliver Wendell Holmes

 

[kimr]

That worked. Thank you so much!!