Explicit conversion

Functions for Manipulating Data in SQL Server

Ana Voicu

Data Engineer

Implicit and explicit conversion

IMPLICIT - performed automatically, behind the scenes
EXPLICIT - performed with the functions CAST() and CONVERT()
- CAST() and CONVERT() are used to convert from one data type to another

CAST()

CAST(expression AS data_type [(length)])

SELECT 
     CAST(3.14 AS int) AS DECIMAL_TO_INT,
     CAST('3.14' AS decimal(3,2)) AS STRING_TO_DECIMAL,
     CAST(GETDATE() AS nvarchar(20)) AS DATE_TO_STRING,
     CAST(GETDATE() AS float) AS DATE_TO_FLOAT;

| DECIMAL_TO_INT | STRING_TO_DECIMAL | DATE_TO_STRING     | DATE_TO_FLOAT    |
|----------------|-------------------|--------------------|------------------|
| 3              | 3.14              | Apr 11 2019 1:01PM | 43531.7052687886 |

CONVERT()

CONVERT(data_type [(length)], expression [,style])

SELECT 
     CONVERT(int, 3.14) AS DECIMAL_TO_INT,
     CONVERT(decimal(3,2), '3.14') AS STRING_TO_DECIMAL,
     CONVERT(nvarchar(20), GETDATE(), 104) AS DATE_TO_STRING,
     CONVERT(float, GETDATE()) AS DATE_TO_FLOAT;

| DECIMAL_TO_INT | STRING_TO_DECIMAL | DATE_TO_STRING | DATE_TO_FLOAT    |
|----------------|-------------------|----------------|------------------|
| 3              | 3.14              | 11.04.2019     | 43531.7052687886 |

CAST() vs. CONVERT()

CAST() comes from the SQL standard and CONVERT() is SQL Server specific
CAST() is available in most database products
CONVERT() performs slightly better in SQL Server

Let's practice!

Functions for Manipulating Data in SQL Server