CAST/CONVERT() in SQL
Converts a value from one data type to another. Both CAST and CONVERT perform the same function with different syntax.
Syntax
Return type
CAST/CONVERT() Function Example
What is CAST/CONVERT() in SQL?
The CAST() and CONVERT() functions in SQL are used to convert a value from one data type to another. These functions are essential for data type compatibility, formatting values, and performing calculations that require specific data types.
CAST()follows ANSI SQL standards and is supported across SQL Server, MySQL, PostgreSQL, and Oracle.CONVERT()is SQL Server-specific and provides additional formatting options for date and numeric conversions.
The CAST() and CONVERT() functions are widely used in data transformation, type conversion, and query optimization.
Parameters:
expression: Value to convert
datatype: Target data type
style (CONVERT only): Optional format style code
Example Use Cases:
-- Date formatting
-- Numeric precision
-- String to number conversion
Notes:
Behavior: Returns error or NULL on invalid conversions
Performance Considerations: Implicit conversions may impact performance
Version Info: Core SQL feature with syntax variations
Deprecated/Recommended Alternatives: Some DBMS prefer specific functions
Error Handling:
Error: Invalid conversion attempts
Recommendation: Validate data before conversion
Supported Databases:
DBMS
Function / Syntax
Example
Behavior with NULL
MySQL
CAST()
CAST(col AS CHAR)
Returns NULL
PostgreSQL
CAST() or ::
CAST(col AS VARCHAR) or col::VARCHAR
Returns NULL
SQL Server
CAST() or CONVERT()
CAST(col AS VARCHAR) or CONVERT(VARCHAR, col)
Returns NULL
SQLite
CAST()
CAST(col AS TEXT)
Returns NULL
BigQuery
CAST() or SAFE_CAST()
CAST(col AS STRING)
Returns NULL
Snowflake
CAST() or ::
CAST(col AS VARCHAR) or col::VARCHAR
Returns NULL
Athena
CAST()
CAST(col AS VARCHAR)
Returns NULL
