CAST in SQL
The CAST function in SQL is used to convert an expression from one data type to another. This is particularly useful in scenarios where data needs to be transformed for comparison, arithmetic operations, or output formatting. The CAST function ensures that the data is handled in the correct format and reduces the likelihood of errors caused by data type mismatches.
Syntax
The CAST function has the following syntax:
CAST(expression AS target_data_type)🧑💻 Where:
- expression is the value or column that you want to convert.
- target_data_type is the data type to which you want to convert the expression.
Example
Let’s assume we have a database table called Orders, which contains information about customer orders. One of the columns, OrderDate, stores dates as text strings, and we would like to convert these text strings into Date data type for easier manipulation.
In the Orders table, column OrderDate stores dates as text and column OrderAmount stores the total amount of the order. We want to extract and convert the dates into a proper Date format and calculate the total amount for all orders within a specific date range.
📌 Query:
SELECT
CAST(OrderDate AS DATE) AS ConvertedOrderDate,
OrderAmount
FROM
Orders
WHERE
CAST(OrderDate AS DATE) BETWEEN '2023-01-01' AND '2023-12-31';📌 Explanation:
- CAST(OrderDate AS DATE) converts the OrderDate column from a text string to a Date format, which helps in performing date comparisons and arithmetic operations.
- The SELECT statement retrieves the converted order date and the order amount.
- The WHERE clause filters the orders to include only those within the specified date range between ‘2023-01-01’ and ‘2023-12-31’.
By using the CAST function, the OrderDate values are now accurately recognized as dates, allowing for proper date range filtering and further date-based operations.