SQL - CAST() and CONVERT() Functions
Type conversion transforms data from one data type to another. SQL handles this through implicit (automatic) and explicit (manual) conversion. Implicit conversion works when SQL Server can safely...
Key Insights
- CAST() is ANSI SQL standard and portable across databases, while CONVERT() is SQL Server-specific with additional formatting capabilities
- Both functions transform data types but CONVERT() provides style parameters for precise date, time, and numeric formatting control
- Understanding implicit vs explicit conversion prevents data loss, performance issues, and runtime errors in production queries
Understanding Type Conversion Fundamentals
Type conversion transforms data from one data type to another. SQL handles this through implicit (automatic) and explicit (manual) conversion. Implicit conversion works when SQL Server can safely convert types without data loss:
-- Implicit conversion - SQL Server handles automatically
SELECT 'Order: ' + CAST(12345 AS VARCHAR(10));
-- This fails - incompatible types
SELECT 'Total: ' + 500.00; -- Error: Conversion failed
-- Explicit conversion required
SELECT 'Total: ' + CAST(500.00 AS VARCHAR(20));
Relying on implicit conversion creates maintenance headaches and performance penalties. The query optimizer cannot efficiently process queries with mixed types, forcing runtime conversions that scan entire tables instead of using indexes.
CAST() Syntax and Common Patterns
CAST() follows ANSI SQL-92 standards, making it portable across PostgreSQL, MySQL, Oracle, and SQL Server:
-- Basic syntax
CAST(expression AS target_datatype[(length)])
-- String to integer
SELECT CAST('12345' AS INT) AS OrderID;
-- Decimal to integer (truncates)
SELECT CAST(99.99 AS INT) AS Price; -- Returns 99
-- Date conversions
SELECT CAST('2024-01-15' AS DATE) AS OrderDate;
-- Handling NULL values
SELECT CAST(NULL AS VARCHAR(50)) AS EmptyString; -- Returns NULL
Common production scenarios require converting query results for application consumption:
-- E-commerce order summary
SELECT
OrderID,
CAST(OrderDate AS DATE) AS OrderDateOnly,
CAST(TotalAmount AS DECIMAL(10,2)) AS FormattedTotal,
CAST(IsProcessed AS VARCHAR(5)) AS ProcessedStatus
FROM Orders
WHERE OrderDate >= CAST('2024-01-01' AS DATETIME);
CONVERT() for SQL Server-Specific Operations
CONVERT() extends CAST() with style parameters for formatting dates and numbers. This SQL Server-specific function provides granular control over output format:
-- Basic syntax
CONVERT(target_datatype[(length)], expression [, style])
-- Date formatting with style codes
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS US_Format; -- 01/15/2024
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS UK_Format; -- 15/01/2024
SELECT CONVERT(VARCHAR(10), GETDATE(), 112) AS ISO_Format; -- 20240115
SELECT CONVERT(VARCHAR(30), GETDATE(), 121) AS ODBC_Format; -- 2024-01-15 14:30:25.123
Style codes eliminate string manipulation for date formatting:
-- Report generation with multiple formats
SELECT
OrderID,
CONVERT(VARCHAR(10), OrderDate, 101) AS DisplayDate,
CONVERT(VARCHAR(8), OrderDate, 108) AS TimeOnly,
CONVERT(VARCHAR(23), OrderDate, 121) AS FullTimestamp
FROM Orders
WHERE OrderDate >= '2024-01-01';
Numeric Conversion and Precision Control
Numeric conversions require careful attention to precision and scale to prevent data loss:
-- Precision matters
SELECT CAST(123.456 AS DECIMAL(5,2)) AS Rounded; -- 123.46
SELECT CAST(123.456 AS INT) AS Truncated; -- 123
-- Money to decimal
DECLARE @Price MONEY = 1234.5678;
SELECT
CAST(@Price AS DECIMAL(10,2)) AS StandardPrice,
CAST(@Price AS INT) AS WholeNumber,
CAST(@Price AS VARCHAR(20)) AS StringPrice;
-- Handling overflow
SELECT TRY_CAST(999999999 AS TINYINT) AS Overflow; -- Returns NULL
Financial calculations demand explicit conversion to prevent rounding errors:
-- Calculate order totals with proper precision
SELECT
o.OrderID,
SUM(CAST(oi.Quantity AS DECIMAL(10,2)) *
CAST(oi.UnitPrice AS DECIMAL(10,2))) AS OrderTotal
FROM Orders o
INNER JOIN OrderItems oi ON o.OrderID = oi.OrderID
GROUP BY o.OrderID;
String Manipulation and Conversion
Converting between strings and other types enables dynamic SQL and data cleansing:
-- String to numeric with validation
SELECT
ProductCode,
CASE
WHEN ISNUMERIC(ProductCode) = 1
THEN CAST(ProductCode AS INT)
ELSE NULL
END AS NumericCode
FROM Products;
-- Building dynamic filters
DECLARE @StartDate VARCHAR(10) = '2024-01-01';
DECLARE @EndDate VARCHAR(10) = '2024-12-31';
SELECT *
FROM Sales
WHERE SaleDate BETWEEN CAST(@StartDate AS DATE)
AND CAST(@EndDate AS DATE);
-- Concatenation with conversion
SELECT
'Customer ' + CAST(CustomerID AS VARCHAR(10)) +
' ordered on ' + CONVERT(VARCHAR(10), OrderDate, 101) AS OrderSummary
FROM Orders;
Performance Implications and Index Usage
Type conversion impacts query performance significantly. Conversions on indexed columns prevent index usage:
-- Bad: Conversion prevents index seek
SELECT *
FROM Orders
WHERE CAST(OrderDate AS DATE) = '2024-01-15';
-- Good: Index can be used
SELECT *
FROM Orders
WHERE OrderDate >= '2024-01-15'
AND OrderDate < '2024-01-16';
-- Bad: Converting column prevents index usage
SELECT *
FROM Products
WHERE CAST(Price AS INT) = 100;
-- Good: Convert the parameter instead
SELECT *
FROM Products
WHERE Price >= 100 AND Price < 101;
Examine execution plans to identify conversion penalties:
-- Enable execution plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Compare performance
SELECT COUNT(*) FROM Orders WHERE CAST(OrderID AS VARCHAR(10)) = '12345';
SELECT COUNT(*) FROM Orders WHERE OrderID = 12345;
Error Handling with TRY_CAST and TRY_CONVERT
SQL Server 2012+ introduced TRY_CAST() and TRY_CONVERT() to handle conversion failures gracefully:
-- Traditional CAST fails on invalid data
SELECT CAST('Invalid' AS INT); -- Error: Conversion failed
-- TRY_CAST returns NULL instead
SELECT TRY_CAST('Invalid' AS INT) AS SafeConversion; -- Returns NULL
-- Data cleansing with TRY_CONVERT
SELECT
CustomerID,
PhoneNumber,
TRY_CAST(PhoneNumber AS BIGINT) AS NumericPhone,
CASE
WHEN TRY_CAST(PhoneNumber AS BIGINT) IS NULL
THEN 'Invalid Format'
ELSE 'Valid'
END AS ValidationStatus
FROM Customers;
Implement robust ETL processes using try functions:
-- Import external data with validation
INSERT INTO CleanedOrders (OrderID, OrderDate, Amount)
SELECT
TRY_CAST(RawOrderID AS INT),
TRY_CONVERT(DATE, RawOrderDate, 101),
TRY_CAST(RawAmount AS DECIMAL(10,2))
FROM StagingOrders
WHERE TRY_CAST(RawOrderID AS INT) IS NOT NULL
AND TRY_CONVERT(DATE, RawOrderDate, 101) IS NOT NULL
AND TRY_CAST(RawAmount AS DECIMAL(10,2)) IS NOT NULL;
Cross-Database Compatibility Strategies
When targeting multiple database platforms, stick with ANSI-standard CAST():
-- Portable across databases
SELECT
OrderID,
CAST(OrderDate AS DATE) AS OrderDateOnly,
CAST(TotalAmount AS DECIMAL(10,2)) AS Amount
FROM Orders;
-- SQL Server specific - not portable
SELECT
OrderID,
CONVERT(VARCHAR(10), OrderDate, 101) AS FormattedDate
FROM Orders;
For applications requiring database portability, abstract formatting in application code rather than SQL. Use CAST() for type conversion and handle formatting at the presentation layer. This separation maintains clean architecture and simplifies database migrations.