Here is an example:

     SELECT (CAST(QTY AS CHAR(3)) || ' units sold of ' || INVN_ID)
       FROM SALES_TBL;

Results:

The format for the CAST command is: CAST(expr AS data_type)

The data type being converted is the source data type and the result data type is the target data type.
An error will occur if expr is not compatible with data_type.
Some data may be truncated and lost, such as converting DECIMAL to INTEGER.
Some data may cause an error if too much data is present, such as FLOAT to SMALLINT.
You can convert character data type to any other data type, provided the data has valid literal values.
When character values are converted, leading and trailing spaces are removed.
Some data can not be converted, such as FLOAT to TIMESTAMP.
If you convert DATE to TIMESTAMP, the time portion will become 00:00:00 which is midnight.
If you convert TIME to TIMESTAMP, the date portion will become the current date.

Examples are: 

     CAST(PRICE AS CHAR(12))          DECIMAL(12,2) to CHAR(12)
     CAST(PRICE AS INTEGER)           DECIMAL(12,2) to INTEGER
     CAST(DESCRIPTION AS CHAR(40))    CHAR(25) to CHAR(40)
     CAST(TAX_PCT AS INTEGER)         SMALLINT to INTEGER
     CAST(CARGO_TONS AS DECIMAL(8))   INTEGER to DECIMAL(8)

> Instead of using the CAST() function, Access uses individual functions such as:
CStr(expr) for string, CInt(expr) for integer, CDec(expr) for decimal number,
Space(number) to add spaces to strings, Left(string, length) to truncate strings, etc.
> DB2 uses TO_CHAR() and TO_DATE().
> MySQL uses SIGNED instead of INTEGER, such as CAST(PRICE AS SIGNED).
> MySQL and SQL Server use CONVERT().
> Oracle uses SUBSTR() to truncate a character string and treats an empty string as NULL.
> Oracle and PostgreSQL uses TO_CHAR, TO_DATE, TO_NUMBER, and TO_TIMESTAMP.

CAST is used to convert one data type to another data type. The most common data types are:

Character String Data:
     CHAR(length)
     VARCHAR(length)
     LONG VARCHAR(length)
     NATIONAL CHARACTER
     Character Large OBject (CLOB)
     NCLOB

Boolean Data

Binary Large OBject (BLOB) Data:
     GRAPHIC(length)
     VARGRAPHIC(length)
     LONG VARGRAPHIC(length)

Exact Numeric Data:
     SMALLINT
     INTEGER
     BIGINT
     DECIMAL(total,fraction) or NUMERIC

Approximate Numeric Data:
     FLOAT(precisionbits)
     REAL
     DOUBLE PRECISION

Date/Time/Timestamp Data:
     DATE
     TIME
     TIME WITH TIME ZONE
     TIMESTAMP

Intervals:
     Year-month
     Day-time

The data you are converting must be compatible with the new data type. Here are some possible conversions:

> Any character type to any other type such as date, time, or numeric.
> Any type to any character type.
> Any numeric type to any other numeric type.
> Any numeric type to an interval such as INTERVAL DAY, or INTERVAL MINUTE.
> Any DATE to a TIMESTAMP where the time portion will be zero.
> Any TIME to a TIME with fractional seconds, or a TIMESTAMP where the date is the current date.
> Any TIMESTAMP to a DATE, TIME, or TIMESTAMP with different fractional seconds.
> Any year-month INTERVAL to another year-month INTERVAL with different leading precision.
> Any day-time INTERVAL to another day-time INTERVAL with different leading precision.
> DB2, MySQL, PostgreSQL, and Sybase support CAST.