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 for CAST: CAST ( <some_value> AS <target_datatype>) -- Syntax for CONVERT: CONVERT ( <target_datatype> ,<some_value> , <optional_style_no> )
Is any valid expression including a variable or a column in a table or a constant value you typed in.
Is the target data type you are converting to. This includes xml, bigint, and sql_variant. Alias data types cannot be used.
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:
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
-- -- -- 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; -- --
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.
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.