Conversion Functions - CAST, CONVERT in SQL

SQL provides conversion functions that allow you to change a value from one data type to another. This is useful when you need to work with data that might not be in the format you need for specific operations. The two most commonly used conversion functions are CAST and CONVERT. Both functions allow you to convert data types but have some differences in syntax and usage. In this article, we will explore both of these functions with examples.

1. CAST - Convert One Data Type to Another

The CAST function is used to explicitly convert a value from one data type to another. It is part of the ANSI SQL standard and works in most relational database management systems (RDBMS).

Syntax:

      CAST(expression AS target_data_type)
    

CAST takes two arguments: the expression to convert and the target_data_type that you want to convert the expression to. The target_data_type can be any valid SQL data type such as INT, VARCHAR, DATE, etc.

Example: Using CAST

      -- Convert a string to an integer
      SELECT CAST('123' AS INT) AS ConvertedValue;
    

This query converts the string '123' into an integer value, resulting in 123.

Example: Using CAST with a Table

      -- Convert a string column to an integer in a table
      SELECT ProductName, CAST(Price AS INT) AS PriceInt
      FROM Products;
    

This query converts the Price column (which might be stored as a string) to an integer for each product in the Products table.

2. CONVERT - Convert One Data Type to Another with Formatting

The CONVERT function is similar to CAST, but it is specific to SQL Server and provides additional functionality, such as the ability to format certain data types, like DATETIME. It is not part of the ANSI SQL standard, so it may not work in all database systems.

Syntax:

      CONVERT(target_data_type, expression, style)
    

      -- Convert a string to an integer
      SELECT CONVERT(INT, '123') AS ConvertedValue;
    

      -- Convert a datetime to a string with a specific format
      SELECT CONVERT(VARCHAR, GETDATE(), 120) AS CurrentDateTime;
    

      -- Convert a datetime column to a string in a table
      SELECT ProductName, CONVERT(VARCHAR, CreatedDate, 103) AS CreatedDateFormatted
      FROM Products;