You can read on the CAST and CONVERT page on the "Truncating and Rounding Results" section.Int, smallint and tinyint will return * when the result length is too short to display when converted to char or varchar. Other numeric to string conversions will return an error.

Luis C.Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Because SQL Server does not automatically do truncation on this type of conversion. The * represents an indication that the VARCHAR isn't large enough to hold the result.

If you want the "right 4", then use RIGHT(263000,4) to do both the conversion and the leading truncation.

Thank you for your reply Jeff. Sorry for not being clear. I understand how to get the last four digits "RIGHT(263000,4)". I am just curious as to why the convert and cast function operates this way and I think you answered that question.

The * represents an indication that the VARCHAR isn't large enough to hold the result.

The asterisk result makes it clear that the VARCHAR is not large enough because if it returned '3000' the end user would think that this is an accurate and valid number.

The back story is, I came across this maintaining someone else's SSIS solution. The cast statement was in the source component script. I don't convert in the source component. I always have a derived column component immediately after the source for conversions, trims, defaults, etc. I was just curious why the result was *. I am more curious why someone would convert in the source component in an SSIS package but that is a question for another forum.

Because SQL Server does not automatically do truncation on this type of conversion. The * represents an indication that the VARCHAR isn't large enough to hold the result.

If you want the "right 4", then use RIGHT(263000,4) to do both the conversion and the leading truncation.

Thank you for your reply Jeff. Sorry for not being clear. I understand how to get the last four digits "RIGHT(263000,4)". I am just curious as to why the convert and cast function operates this way and I think you answered that question.

The * represents an indication that the VARCHAR isn't large enough to hold the result.

The asterisk result makes it clear that the VARCHAR is not large enough because if it returned '3000' the end user would think that this is an accurate and valid number.

The back story is, I came across this maintaining someone else's SSIS solution. The cast statement was in the source component script. I don't convert in the source component. I always have a derived column component immediately after the source for conversions, trims, defaults, etc. I was just curious why the result was *. I am more curious why someone would convert in the source component in an SSIS package but that is a question for another forum.

My 2CentsYou might want to reconsider this, SSIS literally sucks at type conversion compared to SQL Server. Also, sql scripts such as procedures, view and functions carry a lot less TOC on the longer term in terms of maintenance, re usability etc.