Jeff Moden (7/25/2014)
John Mihalko (7/25/2014)
Why does SELECT CONVERT(varchar(4),263000) or SELECT CAST( 263000 AS varchar(4)) return * ?You would think '3000' to be return. :unsure:
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. 🙂