Data Type Conversion Functions
Conversion functions allow you to change the data type of an expression or a column. These functions are essential for manipulating and analyzing data accurately. Here’s an overview of common conversion functions in various SQL databases:
CAST
The CAST function is a standard SQL function used to convert a value from one data type to another.
Syntax:
CAST(expression AS target_data_type)
Examples:
Convert a string to a number:
SELECT CAST('123.45' AS DECIMAL(10, 2)) AS numeric_value;
Converts the string ‘123.45’ to a decimal number.
Convert a string to a date:
SELECT CAST('2024-08-25' AS DATE) AS date_value;
Converts the string ‘2024-08-25’ to a DATE value.
CONVERT (SQL Server, MySQL)
The CONVERT function is used in SQL Server and MySQL and allows specifying a style for certain conversions.
CONVERT(target_data_type, expression [, style])
Examples:
Convert a string to a date with style:
SELECT CONVERT(DATE, '25/08/2024', 103) AS date_value;
The style 103 corresponds to the format dd/mm/yyyy for dates.
Convert a date to a string:
SELECT CONVERT(VARCHAR, GETDATE(), 1) AS date_string;
Converts the current date to a string in the format mm/dd/yy.
TRY_CAST and TRY_CONVERT (SQL Server)
These functions are used to avoid errors during conversion by returning NULL if the conversion fails.
TRY_CAST(expression AS target_data_type) TRY_CONVERT(target_data_type, expression [, style])
Examples:
Try to convert a string to a number:
SELECT TRY_CAST('abc' AS INT) AS numeric_value;
Returns NULL because ‘abc’ cannot be converted to an integer.
Try to convert a string to a date:
SELECT TRY_CONVERT(DATE, '2024-08-25') AS date_value;
Returns the date or NULL if the string is invalid.
TO_CHAR, TO_DATE, TO_NUMBER (Oracle)
These functions are specific to Oracle and are used for converting between data types and formatting.
TO_CHAR(expression [, format]) TO_DATE(expression [, format]) TO_NUMBER(expression [, format])
Examples:
Convert a date to a string with formatting:
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') AS date_string FROM dual;
Converts the current date to a string in the format DD-MON-YYYY.
Convert a string to a date:
SELECT TO_DATE('25-08-2024', 'DD-MM-YYYY') AS date_value FROM dual;
Converts the string ’25-08-2024′ to a date using the format DD-MM-YYYY.
Convert a string to a number:
SELECT TO_NUMBER('1234.56', '9999.99') AS numeric_value FROM dual;
Converts the string ‘1234.56’ to a number with the format 9999.99.
Conversion Functions for Specific Data Types
Conversion Between Numeric Types
ROUND: Round a number to a specified number of decimal places.
SELECT ROUND(123.4567, 2) AS rounded_value;
Rounds 123.4567 to 123.46.
FLOOR and CEIL: Round down or up, respectively.
SELECT FLOOR(123.4567) AS floor_value, CEIL(123.4567) AS ceil_value;
Returns 123 for FLOOR and 124 for CEIL.
Conversion Between Strings and Dates
DATEPART, DATENAME, FORMAT (SQL Server): Extract parts of a date or format a date.
SELECT DATEPART(YEAR, GETDATE()) AS year_part, DATENAME(MONTH, GETDATE()) AS month_name, FORMAT(GETDATE(), 'dd-MM-yyyy') AS formatted_date;
Returns the year, the month name, and a formatted date.
Using Conversion Functions in Queries
Data Manipulation
Conversion functions are frequently used to manipulate data during retrieval or insertion.
Example:
To display prices in dollars formatted to two decimal places:
SELECT product_name, CONVERT(VARCHAR, CAST(price AS DECIMAL(10, 2)), 1) AS formatted_price FROM products;
Data Comparison
Conversion functions can be used to compare data of different types.
Example:
To compare date stored as strings with actual dates
SELECT * FROM orders WHERE CAST(order_date AS DATE) > GETDATE();
This returns orders where the order date is greater than the current date.
Conclusion
Conversion functions are crucial for handling different data types in SQL. They enable you to transform, manipulate, and compare data as needed. Understanding and using these functions appropriately allows you to work effectively with diverse data types and formats in your SQL queries.