Conversion and Configuration Function in SQL Server

In this article, we discuss conversion and configuration functions in SQL Server.

CONVERSION FUNCTIONS

• Conversion functions are single row functions that are capable of typecasting column value or an expression.
• Conversion Functions:- CAST(), CONVERT(), and TRY_CAST(), TRY_CONVERT(),

CAST() and CONVERT()

• CAST and CONVERT functions used to convert one data type to another data type. It is a way to write program procedures or queries.
• The CAST function converts a data type without a specific format.
• The CONVERT function does convert and formatting data types at the same time.
• If the conversion fails, the function will return an error. Otherwise, it will return the converted value.

Syntax of CAST():- CAST( expression AS type [ (length) ] )

Syntax of CONVERT():- CONVERT( type [ (length) ], expression [ , style ] )

DIFFERENCE BETWEEN CAST() and CONVERT() FUNCTION

• CAST is also less powerful and less flexible than CONVERT.
• CAST is an ANSI standard while CONVERT is a specific function in the SQL server.
• CONVERT function used for formatting purposes and CAST is used to remove or reduce format while still converting.
• CONVERT can stimulate set date format options while CAST cannot do this function.

TRY_CAST() and TRY_CONVERT()

• The TRY_CAST() function to cast a value of one type to another.
• The TRY_CONVERT() function converts a value of one type to another.
• If the conversion fails, it returns NULL.

Syntax of TRY_CAST():- TRY_CAST ( expression AS data_type [ ( length ) ] )

Syntax of TRY_CONVERT():-

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

CONFIGURATION FUNCTIONS

• SQL Server configuration functions return information about current configuration option settings.

@@VERSION

• It returns the version, processor architecture, build date, and operating system for the current installation of SQL Server.
• Syntax: @@VERSION

If you are new to database learning — SQL Server recommended is the following must-watch the video: -