TO_DATE
Purpose: Converts a string to a date format.
Syntax:
TO_DATE(expression [, format_model [, 'nls_param']])
expression: The string to convert into a date.
format_model: Specifies the date format to use for parsing the string.
‘nls_param‘: Optional NLS parameters to specify locale-specific settings.
Examples:
Convert String to Date:
SELECT TO_DATE('2024-08-25', 'YYYY-MM-DD') AS date_value
FROM dual;
Converts ‘2024-08-25’ to a DATE type.
Convert String with Time:
SELECT TO_DATE('2024-08-25 15:30:00', 'YYYY-MM-DD HH24:MI:SS') AS datetime_value
FROM dual;
Converts ‘2024-08-25 15:30:00’ to a DATE type with time.
TO_TIMESTAMP
Purpose: Converts a string to a timestamp format.
Syntax:
TO_TIMESTAMP(expression [, format_model [, 'nls_param']])
expression: The string to convert into a timestamp.
format_model: Specifies the timestamp format to use.
‘nls_param‘: Optional NLS parameters.
Examples:
Convert String to Timestamp:
SELECT TO_TIMESTAMP('2024-08-25 15:30:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp_value
FROM dual;
Converts ‘2024-08-25 15:30:00’ to a TIMESTAMP type.
TO_CHAR
Purpose: Converts a date or number to a string with a specified format.
Syntax:
TO_CHAR(expression [, format_model [, 'nls_param']])
expression: The date or number to convert to a string.
format_model: Specifies the format for the output string.
‘nls_param‘: Optional NLS parameters.
Examples:
Convert Date to String:
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') AS formatted_date FROM dual;
Converts the current date to a string format like 25-AUG-2024.
Convert Number to String:
SELECT TO_CHAR(1234567.89, '9,999,999.99') AS formatted_number FROM dual;
Converts 1234567.89 to a string with thousands separators and decimal formatting.
Additional Conversion Functions in SQL Server
CAST
Purpose: Converts an expression to a specified data type.
Syntax:
CAST(expression AS target_data_type)
Examples:
Convert String to Date:
SELECT CAST('2024-08-25' AS DATE) AS date_value;
Converts the string ‘2024-08-25’ to a DATE type.
Convert Date to String:
SELECT CAST(GETDATE() AS VARCHAR(30)) AS date_string;
Converts the current date and time to a string.
CONVERT
Purpose: Converts an expression to a specified data type with optional style formatting.
Syntax:
CONVERT(target_data_type, expression [, style])
Examples:
Convert Date to String with Format:
SELECT CONVERT(VARCHAR, GETDATE(), 103) AS formatted_date;
Converts the current date to a string with the format dd/mm/yyyy.
Convert String to Numeric:
SELECT CONVERT(NUMERIC, '1234.56') AS numeric_value;
Converts the string ‘1234.56’ to a numeric value.
Additional Conversion Functions in MySQL
CAST
Purpose: Converts a value to a specified data type.
Syntax:
CAST(expression AS target_data_type)
Examples:
Convert String to Integer:
SELECT CAST('123' AS UNSIGNED) AS integer_value;
Converts the string ‘123’ to an integer.
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 ‘2024-08-25’ to a date.
Convert Number to String:
SELECT CONVERT(1234.56, CHAR) AS string_value;
Converts the number 1234.56 to a string.
Additional Conversion Functions in PostgreSQL
CAST
Purpose: Converts an expression to a specified data type.
Syntax:
CAST(expression AS target_data_type)
Examples:
Convert String to Date:
SELECT CAST('2024-08-25' AS DATE) AS date_value;
Converts the string ‘2024-08-25’ to a DATE type.
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 method for type casting.
Syntax
expression::target_data_type
Examples:
Convert String to Numeric:
SELECT '1234.56'::NUMERIC AS numeric_value;
Converts the string ‘1234.56’ to a numeric type.
Convert String to Date:
SELECT '2024-08-25'::DATE AS date_value;
Converts the string ‘2024-08-25’ to a date type.
Summary
Conversion functions are vital for transforming data between different types to meet the needs of various operations. They help in ensuring that data is correctly formatted and compatible with the required operations. Each SQL database offers different functions and syntaxes to achieve similar results, providing flexibility in handling diverse data types.