replace function and varchar

[bobbo]

if (as per books online) the replace function returns a varchar, why do these commands give different results?

print Replace(Convert(varchar(30),cast('2010-01-01 00:00:00' as datetime) , 120), ' 00:00:00','') + '.'
print Replace(Convert(varchar(30),cast('2010-01-01 00:00:00' as datetime) , 120), '00:00:00','') + '.'

[rmiao]

Sql doesn't remove space in middle of the string.

[bobbo]

It's not in the middle. forget about the dot at the end. the replace function returns a space as the last character in the second example.

[skhanal]

REPLACE is not trimming the last blank space, it could be a bug.

[rmiao]

It works ok if you take out the dot at the end, try following:

select len(Replace(Convert(varchar(30),cast('2010-01-01 00:00:00' as datetime) , 120), ' 00:00:00','')+ '.')
select len(Replace(Convert(varchar(30),cast('2010-01-01 00:00:00' as datetime) , 120), '00:00:00','')+ '.')
select len(Replace(Convert(varchar(30),cast('2010-01-01 00:00:00' as datetime) , 120), ' 00:00:00',''))
select len(Replace(Convert(varchar(30),cast('2010-01-01 00:00:00' as datetime) , 120), '00:00:00',''))

[CustardCream]

The replaces that you are performing are different, the first the space and the time ' 00:00:00',
the second just the time '00:00:00'.