SQL CONVERT Function

The SQL CONVERT function is used to change the data type of a value to another data type. It is commonly used to convert between numeric, string, and date/time values. The CONVERT function provides more formatting options than CAST, making it useful for specific output formats.

In this tutorial, we will explore the SQL CONVERT function, its syntax, and practical examples demonstrating how to use it effectively.

Syntax of SQL CONVERT Function

The basic syntax of the CONVERT function is:

</>

Copy

CONVERT(data_type(length), expression, style)

Parameters:

data_type(length): The target data type to which the expression is converted.
expression: The value or column that needs to be converted.
style: (Optional) Used when converting date/time values to different formats.

Step-by-Step Examples Using SQL CONVERT

1. Converting Date to a String Format

Let’s create an employees table and insert some sample data:

In SQL Server:

</>

Copy

CREATE TABLE employees (
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(50),
    joining_date DATETIME
);

Insert some sample data:

</>

Copy

INSERT INTO employees (name, joining_date)
VALUES 
('Arjun', '2024-02-01 10:30:00'),
('Ram', '2023-08-15 09:00:00'),
('Priya', '2022-12-10 15:45:00');

Now, let’s use CONVERT to display the joining date in a different format:

</>

Copy

SELECT name, 
       joining_date, 
       CONVERT(VARCHAR, joining_date, 103) AS formatted_date
FROM employees;

Explanation:

CONVERT(VARCHAR, joining_date, 103) converts the joining_date into a **DD/MM/YYYY** format.
The 103 style format outputs the date as dd/mm/yyyy.
This is useful for displaying dates in a user-friendly format.

2. Converting an Integer to a String

Let’s create a sales table and insert some sample data:

</>

Copy

CREATE TABLE sales (
    id INT PRIMARY KEY IDENTITY(1,1),
    customer_name VARCHAR(50),
    total_amount INT
);

Insert some sample data:

</>

Copy

INSERT INTO sales (customer_name, total_amount)
VALUES 
('Arjun', 5000),
('Ram', 7200),
('Priya', 3000);

Now, let’s convert the integer value total_amount into a string format:

</>

Copy

SELECT customer_name, 
       total_amount, 
       CONVERT(VARCHAR, total_amount) AS total_amount_string
FROM sales;

Explanation:

CONVERT(VARCHAR, total_amount) changes the integer column into a string format.
This is useful when concatenating numeric values with text.

Conclusion

The SQL CONVERT function is useful for changing data types within queries. In this tutorial, we covered:

The syntax and purpose of the CONVERT function.
Using CONVERT to format date values.
Converting an integer to a string for text-based output.

Mastering the CONVERT function allows for better control over data presentation and formatting in SQL queries.

TutorialKart

SQL CONVERT Function