CAST in SQL: Complete Guide with Examples & Best Practices | Free
Write Your First SQL Query in 10 Seconds—Free
Basic CAST Usage
Convert data types effectively using the CAST function. For more tips, explore our AI SQL Tools for automatic query optimization.
Simple Type Conversions
— Convert string to integer SELECT CAST(‘123’ AS INT) as converted_number;
— Convert number to string SELECT CAST(123 AS VARCHAR) as converted_string;
— Convert string to date SELECT CAST(‘2024-01-15’ AS DATE) as
Database-Specific Syntax
SQL Server
SELECT CAST(price AS DECIMAL(10,2)) as formatted_price FROM
MySQL
SELECT CAST(price AS DECIMAL(10,2)) as formatted_price FROM
PostgreSQL
SELECT CAST(price AS NUMERIC(10,2)) as formatted_price FROM
For more examples, check out SQL Query Optimization Techniques to make your database faster.
Common Use Cases
1. Numeric Formatting
SELECT product_name, CAST(price AS DECIMAL(10,2)) as formatted_price FROM
2. Date Manipulations
SELECT order_date, CAST(EXTRACT(YEAR FROM order_date) AS VARCHAR) as order_year FROM
3. String Operations
SELECT ‘Order #’ + CAST(order_id AS VARCHAR) as order_reference FROM
Advanced Applications
Dynamic SQL
DECLARE @sql_query NVARCHAR(MAX); DECLARE @table_name VARCHAR(50) = ‘orders’;
SET @sql_query = ’ SELECT * FROM ’ + @table_name + ’ WHERE order_date >= CAST(DATEADD(day, -30, GETDATE()) AS DATE)’;
EXEC
Learn more about SQL AI Solutions for building dynamic queries efficiently.
Performance Optimization
Index Usage
ALTER TABLE orders ADD order_date_only AS CAST(order_datetime AS DATE) PERSISTED;
CREATE INDEX idx_order_date ON orders(order_date_only)
Batch Processing
UPDATE sales_import SET amount = CAST(amount_string AS DECIMAL(10,2)), transaction_date = CAST(date_string AS DATE) WHERE TRY_CAST(amount_string AS DECIMAL(10,2)) IS NOT NULL
Best Practices
1. Error Handling
SELECT TRY_CAST(value AS INT) as safe_integer, CASE WHEN TRY_CAST(value AS INT) IS NULL THEN ‘Invalid Number’ ELSE ‘Valid Number’ END as validation_result FROM
2. Data Validation
SELECT column_value, CASE WHEN REGEXP_LIKE(column_value, ’^[0-9]+$’) THEN CAST(column_value AS INT) ELSE NULL END as converted_value FROM
3. Performance Considerations
CREATE TABLE performance_example (
id INT,
price DECIMAL(10,2),
description VARCHAR(100)
);
CREATE INDEX idx_price ON performance_example(price)
FAQs
Q: When should I use CAST vs CONVERT?
A: CAST is ANSI standard and works across databases. Use CONVERT for database-specific features.
Q: Can CAST affect performance?
A: Yes, especially in WHERE clauses. Create computed columns for frequent conversions.
Need help? Discover more about AI SQL Tools to streamline SQL queries and improve performance.
For complex query optimizations, visit our SQL Optimization Guide.
Start your free trial
Share this
More Articles
More Articles
More Articles
TOOLS
Build Your Own AI Agent Team in 15 Min — Free OpenClaw Guide
Build Your Own AI Agent Team in 15 Min — Free OpenClaw Guide
Feb 5, 2026
TOOLS
OpenClaw AI Assistant: Local 24/7 Automation Guide 2026
OpenClaw AI Assistant: Local 24/7 Automation Guide 2026
Feb 4, 2026
TOOLS
SQL WITH Clause (CTE): Complete Guide with Examples
SQL WITH Clause (CTE): Complete Guide with Examples
Jan 14, 2026
TOOLS
MySQL to PostgreSQL Migration: Complete 2026 Guide with Syntax Conversion
MySQL to PostgreSQL Migration: Complete 2026 Guide with Syntax Conversion
Jan 14, 2026
TOOLS
SQL vs Excel: When Should You Make the Switch? [2026]
SQL vs Excel: When Should You Make the Switch? [2026]
Jan 14, 2026
Copyright © AI2sql 2026
Cross Regions Technology
13553 Atlantic Blvd, Suite 201
FL 32225
Company