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.

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 (-) sign. 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

See Also