Converting Character Data
When character expressions are converted to a character data type of a different size, values too long for the new data type are truncated.
When a character expression is converted to a character expression of a different data type or size (such as from char(5) to varchar(5), or char(20) to char(15)), the collation of the input value is assigned to the converted value. If a non-character expression is converted to a character data type, the default collation of the current database is assigned to the converted value. In either case, you can assign a specific collation using the COLLATE clause.
Note Code page translations are supported for char and varchar data types, but not for text data type. As with previous versions of Microsoft® SQL Server™, data loss during code page translations are not reported.
Character expressions being converted to an approximate numeric data type can include optional exponential notation (a lowercase e or uppercase E followed by an optional plus (+) or minus (-) sign and then a number).
Character expressions being converted to an exact numeric data type must consist of digits, a decimal point, and an optional plus (+) or minus (-). Leading blanks are ignored. Comma separators (such as the thousands separator in 123,456.00) are not allowed in the string.
Character expressions being converted to money or smallmoney data types can also include an optional decimal point and dollar sign ($). Comma separators (as in $123,456.00) are allowed.
This example shows how to convert data for display. This example converts sales data to character data prior to performing a string comparison and converts the current date to style 3, dd/mm/yy.
USE pubs
GO
SELECT title,
CAST(ytd_sales AS CHAR(12)),
CAST(GETDATE() AS CHAR(12))
FROM titles
WHERE CAST(ytd_sales AS CHAR(20) ) LIKE '1%'
GO
This example converts a uniqueidentifier value to a char data type.
DECLARE @myid uniqueidentifier
SET @myid = NEWID()
SELECT CONVERT(char(255), @myid) AS 'Char'
GO
This example converts the current date to style 3, dd/mm/yy.
SELECT CONVERT(char(12), GETDATE(), 3)
GO