In SQL, the CONVERT and CAST functions are used to change the data type of a value from one type to another. Both functions are commonly used for data type conversions, but they have slightly different syntax and capabilities.
1. Using CAST
The CAST function is used to convert one data type into another. Its syntax is as follows:
CAST(expression AS target_data_type)
- expression: The value or column you want to convert.
- target_data_type: The data type you want to convert the expression to (e.g., VARCHAR, INTEGER, DATE, etc.).
Example:
SELECT CAST('2023-01-01' AS DATE) AS ConvertedDate;
This will convert the string '2023-01-01' into a DATE type.
2. Using CONVERT
The CONVERT function also converts a value from one data type to another but offers an additional style parameter that can be used with date/time conversions. Its syntax is:
CONVERT(target_data_type, expression [, style])
- target_data_type: The data type you want to convert to.
- expression: The value or column you want to convert.
- style (optional): A code indicating the format to use for the conversion (primarily used with date/time conversions).
Example:
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS ConvertedDate;
This converts the current date to a VARCHAR formatted as MM/DD/YYYY using style code 101.
Key Differences:
- CAST uses a simpler syntax and is SQL standard compliant, making it widely supported across different SQL databases.
- CONVERT allows for additional formatting options (via the style parameter) that can be particularly useful for date and time conversions.
Additional Examples:
1. Convert an integer to a string:
Using CAST:
SELECT CAST(123 AS VARCHAR(10)) AS StringValue;
Using CONVERT:
SELECT CONVERT(VARCHAR(10), 123) AS StringValue;
2. Convert a string to an integer:
Using CAST:
SELECT CAST('456' AS INT) AS IntValue;
Using CONVERT:
SELECT CONVERT(INT, '456') AS IntValue;
3. Convert a string to a date:
Using CAST:
SELECT CAST('2023-10-01' AS DATE) AS DateValue;
Using CONVERT:
SELECT CONVERT(DATE, '10/01/2023', 101) AS DateValue; -- MM/DD/YYYY format
In summary, you can use either CAST or CONVERT for data type conversions, choosing based on your specific requirements, including formatting needs.