The Microsoft® SQL Server™ CAST and CONVERT functions are multiple purpose conversion functions. They convert an expression of one data type to another data type. In addition, CONVERT supports a variety of special date formats.
CAST (expression AS datatype)
CONVERT (data_type[(length)], expression [, style])
They perform the same operations as a number of Oracle functions.
| Conversion | Oracle | SQL Server |
|---|---|---|
| Character to number | TO_NUMBER(expression) | CAST(expression AS decimal) CAST(expression AS integer) |
| Number to character | TO_CHAR(expression) | CAST(expression AS char) CAST(expression AS varchar) CAST(expression AS nvarchar) |
| Character to date | TO_DATE('04-JUL-97') TO_DATE('04-JUL-1997', 'dd-mon-yyyy') TO_DATE('July 4, 1997', 'Month dd, yyyy') |
CAST(expression AS datetime) |
| Date to character | TO_CHAR(expression) TO_CHAR(expression, 'dd mon yyyy') TO_CHAR(expression, 'mm/dd/yyyy') |
CONVERT(char, expression) CONVERT(char, expression, 106) CONVERT(char, expression, 101) |
| Hex to binary | HEXTORAW(expression) | CAST(expression AS binary) |
| Binary to hex | RAWTOHEX(expression) | CONVERT(binary_expression AS char) |
| Character to ROWID | CHARTOROWID | N/A |
| ROWID to character | ROWIDTOCHAR | N/A |
| Convert one character set to another | CONVERT | N/A |
| Single-byte characters to multibyte | TO_MULTI_BYTE | N/A |
| Multibyte characters to single-byte | TO_SINGLE_BYTE | N/A |
| Character set to national character set or vice versa | TRANSLATE USING | N/A |
Character strings are converted to dates. In Oracle, the default date format model is DD-MON-YY. If you use any other format, you must provide an appropriate date format model. The CAST and CONVERT functions convert standard date formats automatically, without the need for a format model.
When converting from a date to a character string, the default output for the CONVERT function is dd mon yyyy hh:mm:ss:mmm(24h). A numeric style code is used to format the output to other types of date format models.
| CAST and CONVERT | Conversion Functions |