CONVERT

The SQL CONVERT function is used to convert an expression from one data type to another. It’s a powerful function that allows you to handle data type conversions and format the output according to a specified style.

Syntax

The basic syntax of the CONVERT function is as follows:

CONVERT(data_type, expression, style)

data_type: The target data type to which you want to convert the expression.
expression: The expression or value to be converted.
style: (Optional) This parameter is used when converting from date and time data types. It specifies the output format.

Example

Let’s explore a few examples of using the CONVERT function:

Converting a Date to a String

DECLARE @CurrentDate DATETIME = GETDATE();
SELECT CONVERT(VARCHAR, @CurrentDate, 101) AS FormattedDate;

In this example, 101 is the style code for the USA date format (mm/dd/yyyy). You can choose different style codes based on the desired output format.

Converting a String to a Date

DECLARE @DateString VARCHAR(10) = '2023-01-01';
SELECT CONVERT(DATE, @DateString, 120) AS ConvertedDate;

Here, 120 is the style code for the ODBC canonical date-time format.

Converting Numeric Data Types

DECLARE @Number INT = 12345;
SELECT CONVERT(VARCHAR, @Number) AS ConvertedNumber;

This example converts an integer to a varchar.

Converting between Time Data Types

DECLARE @CurrentTime TIME = GETDATE();
SELECT CONVERT(VARCHAR, @CurrentTime, 108) AS FormattedTime;

Here, 108 is the style code for the hh:mm:ss format.

It’s important to note that not all conversions are possible, and some may result in data loss or errors. For instance, converting a string that doesn’t represent a valid date to a date type might fail. Always ensure that the conversion makes sense for your data and handle potential errors appropriately.

Additionally, you can use the CAST function for similar purposes, and the choice between CONVERT and CAST often comes down to personal preference and specific requirements.