Home > Documentation > SQL Server

Cast vs Convert in SQL Server

In SQL Server both CAST and CONVERT functions are used to convert data from one type to another. The functions can be called from stored procedures, functions, triggers and views. Their capabilities are similar, yet there are some differences illustrated by this table:

CAST		CONVERT

CAST ( expression AS data_type [ ( length ) ] )		CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
ANSI standard		SQL Server specific function
does not have formatting features		can be used for formatting purposes especially for date/time and money types

SQL Server allows implicit conversions between some data types that do not require using either CAST or CONVERT function. On the other hand, explicit conversions require using the CAST or the CONVERT. The following table illustrates all possible implicit and explicit conversions for SQL Server system (standard) data types:

(Image is taken from the official Microsoft site https://www.microsoft.com)

Examples

The following example demonstrates applying CAST to different SQL Server data types:

SELECT CAST(12.3456 AS INT) AS val1, 
       CAST(12.3456 AS MONEY) AS val2, 
       CAST(12.3456 AS NUMERIC) AS val3, 
       CAST('2020-08-29 11:22:33' AS date) AS val4, 
       CAST('2020-08-29' AS datetime) AS val5, 
       CAST('2020-08-29 11:22:33' AS time) AS val6

The result of that query is:

val1       val2    val3          val4                     val5                val6
----  ---------  ------  ------------  -----------------------  ------------------
  12    12.3456      12    2020-08-29  2020-08-29 00:00:00.000    11:22:33.0000000

And this is example of using CONVERT to format date values:

SELECT CONVERT(varchar, getdate(), 113);
GO
----------------------------------------
27 Jul 2022 16:41:53:500

Have any questions? Contact us