Type conversion functions in SQL Server

Hi All,

Hope you all are doing good.

In this article, we will know about type conversion functions in SQL Server.

We can use CAST and CONVERT functions to convert one data type to another.

Syntaxes:
CAST ( expression AS data_type [ ( length ) ] )
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Style is an optional parameter of CONVERT function.

Examples:
SELECT CAST (GETDATE() as nvarchar) -- Jan 27 2020 10:54PM
SELECT CONVERT (nvarchar, GETDATE()) -- Jan 27 2020 10:54PM

SELECT CAST (GETDATE() as DATE)         -- 2020-01-27
SELECT CONVERT (DATE, GETDATE()) -- 2020-01-27

SELECT CONVERT (VARCHAR(10),GETDATE(),101) -- mm/dd/yyyy -- 01/27/2020
SELECT CONVERT (VARCHAR(10),GETDATE(),102) -- yy.mm.dd -- 2020.01.27
SELECT CONVERT (VARCHAR(10),GETDATE(),103) -- dd/mm/yyyy -- 27/01/2020
SELECT CONVERT (VARCHAR(10),GETDATE(),104) -- dd.mm.yy -- 27.01.2020
SELECT CONVERT (VARCHAR(10),GETDATE(),105) -- dd-mm-yy -- 27-01-2020

Differences between CAST and CONVERT functions.

CAST	CONVERT
Cast is based on ANSI standard. If portability is a concern and if you want to use the script with other database applications then use CAST()	Convert is specific to SQL Server
CAST is less flexible than CONVERT. For example, it's possible to control how you want DateTime datatypes to be converted using styles with convert function.	CONVERT provides more flexibility than CAST.

CAST() is more preferable, unless you will take the advantage of the style functionality in CONVERT().

Thanks for reading my article.