TO_CHAR
Purpose: Converts numbers or dates to strings, with optional formatting.
Syntax:
TO_CHAR(expression [, format])
expression: The value to convert.
format (optional): Specifies the format model.
Examples:
Convert Date to String:
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') AS formatted_date FROM dual;
Converts the current date to the format 25-AUG-2024.
Convert Number to String
SELECT TO_CHAR(1234567.89, '9,999,999.99') AS formatted_number FROM dual;
Converts the number to a string with thousand separators and two decimal places.
TO_NUMBER
Purpose: Converts strings to numbers, with optional formatting.
Syntax:
TO_NUMBER(expression [, format])
expression: The string to convert.
format (optional): Specifies the format model.
Examples:
Convert String to Number:
SELECT TO_NUMBER('1234.56') AS numeric_value
FROM dual;
Converts the string ‘1234.56’ to a number.
Convert Formatted String to Number:
SELECT TO_NUMBER('1,234.56', '9,999.99') AS numeric_value
FROM dual;
Converts the string ‘1,234.56’ to a number, using a format with commas.
TO_DATE
Purpose: Converts strings to date values, with optional formatting.
Syntax:
TO_DATE(expression [, format])
expression: The string to convert.
format (optional): Specifies the format model.
Examples:
Convert String to Date:
SELECT TO_DATE('25-08-2024', 'DD-MM-YYYY') AS date_value
FROM dual;
Converts the string ’25-08-2024′ to a date.
Convert String with Time to Date:
SELECT TO_DATE('25-08-2024 15:30:00', 'DD-MM-YYYY HH24:MI:SS') AS date_time_value
FROM dual;
Converts the string to a date with time.
SQL Server Conversion Functions
CAST
Purpose: Converts an expression to a specified data type.
Syntax:
CAST(expression AS target_data_type)
expression: The value to convert.
target_data_type: The type to convert to.
Examples:
Convert String to Integer:
SELECT CAST('123' AS INT) AS integer_value;
Converts the string ‘123’ to an integer.
Convert Date to String:
SELECT CAST(GETDATE() AS VARCHAR(10)) AS date_string;
Converts the current date to a string.
CONVERT
Purpose: Converts an expression to a specified data type with optional style for formatting.
Syntax:
CONVERT(target_data_type, expression [, style])
target_data_type: The type to convert to.
expression: The value to convert.
style (optional): A style code for formatting.
Examples:
Convert Date to String with Format
SELECT CONVERT(VARCHAR, GETDATE(), 103) AS date_string;
Converts the current date to a string in dd/mm/yyyy format.
Convert String to Date:
SELECT CONVERT(DATE, '2024-08-25') AS date_value;
Converts the string to a date.
MySQL Conversion Functions
CAST
Purpose: Converts a value to a specified data type.
Syntax
CAST(expression AS target_data_type)
Examples:
Convert String to Decimal
SELECT CAST('1234.56' AS DECIMAL(10,2)) AS numeric_value;
Converts the string ‘1234.56’ to a decimal.
Convert Date to String
SELECT CAST(NOW() AS CHAR) AS date_string;
Converts the current date and time to a string.
CONVERT
Purpose: Converts a value to a specified data type or character set.
Syntax:
CONVERT(expression, target_data_type)
Examples:
Convert String to Date:
SELECT CONVERT('2024-08-25', DATE) AS date_value;
Converts the string to a date.
Convert Number to String:
SELECT CONVERT(1234.56, CHAR) AS string_value;
Converts the number to a string.
PostgreSQL Conversion Functions
CAST
Purpose: Converts an expression to a specified data type.
Syntax:
CAST(expression AS target_data_type)
Examples:
Convert String to Integer:
SELECT CAST('123' AS INTEGER) AS integer_value;
Converts the string ‘123’ to an integer.
Convert Date to String:
SELECT CAST(NOW() AS TEXT) AS date_string;
Converts the current date and time to a string.
Operator
Purpose: A shorthand for casting.
Syntax:
expression::target_data_type
Examples:
Convert String to Decimal:
SELECT '1234.56'::NUMERIC AS numeric_value;
Converts the string ‘1234.56’ to a numeric value.
Convert String to Date:
SELECT '2024-08-25'::DATE AS date_value;
Converts the string to a date.
Summary
Conversion functions are essential for ensuring that data is correctly formatted and compatible with the operations you want to perform. They help in transforming data types to fit different contexts, such as displaying formatted dates, performing calculations, or storing data in the desired format.