CAST and CONVERT (T-SQL)

CAST and CONVERT

CAST and CONVERT functions convert an expression of a particular data type into another data type.  The CAST function is standards based so affords more portability outside the of the Microsoft SQL Server environment. The  CONVERT function is NOT standards based so its use and function may differ from other implementations on other platforms.

Syntax

-- Syntax for CAST:  
CAST ( <some_value> AS <target_datatype>)  
  
-- Syntax for CONVERT:  
CONVERT ( <target_datatype> ,<some_value> , <optional_style_no> ) 
    

Arguments defined:

<some_value>
Is any valid expression including a variable or a column in a table or a constant value you typed in.

<target_datatype>
Is the target data type you are converting to. This includes xml, bigint, and sql_variant. Alias data types cannot be used.

<optional_style_no>
The style number can be used to customize the output. It is an integer expression that specifies how the CONVERT function is to translate expression. If style is NULL, NULL is returned. See links to style charts below:

Style Charts:

Date and Time – When <some_value> expression is a date or time data type

Float and Real –  When <some_value> expression is float or real

Money and Smallmoney – When <some_value> expression is money or smallmoney

Examples:

 

--
--
-- CAST Function
-- Converting Datetime2 datatype to Date datatype
SELECT CAST(SYSDATETIME() AS DATE);
--
--
-- CONVERT function
-- Converting Datetime2 datatype to Date datatype
SELECT CONVERT(DATE, SYSDATETIME());
--
-- CONVERT with datetime data and a style option
--
SELECT  CONVERT(datetime, '20120212', 103) AS British_French_Style ;
--
SELECT CONVERT(CHAR(8), CURRENT_TIMESTAMP,101) AS US_Style;
--
--

Remarks

The CAST function is ANSI standards based and therefore would be the recommended function.

Because the Microsoft version of CONVERT has the additional <optional_style_no> for formatting output it has added functionality and appeal.

Both of these functions are scalar in nature an therefore can be used in either the SELECT or WHERE clauses.

The use of conversion functions in the WHERE clause may cause performance issues because they may prevent query optimization from utilizing available indexes.

Parting Shot

Hey everyone. Thanks for reading this it is always fun to write this stuff. I hope you enjoyed the quick read and think about this:

~ It’s so foggy outside the birds are walking.

 

See Also:

Oracle / PLSQL: CONVERT

MySQL: CAST and CONVERT

Share the Knowledge

Leave a Reply