SQL SERVER – EXPLICIT Conversion - CONVERT() vs. CAST()

Convert() and Cast() Both the functions are used to convert the data of a column/output parameter of one data type to another data type explicitly.

The main difference between these two is the syntax.

Syntax :                                                                                   Convert(DataType,Expression,Style)                            CAST(Expression as DataType)

CONVERT function can be  used to convert the output to any format we like. Convert is specific to SQL and more flexible while converting Date time, fractional and monetary values.

The CONVERT function's third optional parameter called ‘style’ plays a key role in formatting the value while  converting the  a date, money, float or real value to a VARCHAR.

If formatting is not an issue then, perhaps the CAST function would be better to use. Cast is more ANSI standard , more portable.

CAST() is required when converting between decimal and numeric to Preserve the Number of Decimal Places in original expression.

Suppose we want to calculate the Exact number of working days of an employee by using his total working hours in a month.Assuming that the number of working hours per day is 9 and the number of working hours of the employee in a month is 185. Now In order to get the Exact Number of working days of an employee, we will divide the total working hours of the employee by the working hours per day i.e.  9

Select 185/9 'No. of working days'

Output:

On observing the above result, we can found that the system has returned the approximate integer value instead of the Exact decimal  value. In order to get the exact value, we have to use the Cast() function where we convert the integer value to decimal.

Select CAST(185 as Decimal)/9  'No. of working days'

Output:

CAST() can also be used to Truncate the Decimal Values. If we used ROUND() function to truncate the decimal values, still we are able to see the zeros after the decimal as shown below.

Select Round(185.23,0) 'Truncate'

Output:

To avoid the zeros, We can use either CAST() or Convert() function. The following examples are to truncate the data to 0 or 2 decimal places

Select CAST(185.235467 as INT) 'Truncate'
Select CAST(185.235467 as DECIMAL(5,2)) 'Truncate'  Select CONVERT(DECIMAL(5,2),185.235467)  'Truncate' 

Output:

Similarly CAST() can also be used to Concatenate Different Data Types. Let us take an example of AlphaNumeric Identity Column whose values are the concatenation of an integer and a string.

Here If we tried to concatenate an integer to a  string, it throws an error message as shown below.

Select 'EMP'  + 1

Output:

On using the CAST() function,

Select  'EMP' + CAST(1 as VARCHAR)  'EMPID'

Output:

The Convert() function is used to change the Datetime Datatype to Varchar. It can be used to get the Date and time values separately.

Example:

Select  Convert(VARCHAR(10),GetDate(),101)'Date(mm/dd)'
Select  Convert(VARCHAR(10),GetDate(),103)'Date(dd/mm)'

Output: