CAST and CONVERT change data types. Essential for type-safe operations and formatting.

CAST Syntax

SELECT CAST(column AS datatype) FROM table;

String to Number

SELECT CAST('123' AS INT);

SELECT CAST('123.45' AS DECIMAL(10,2));

Number to String

SELECT CAST(price AS CHAR) FROM products;

SELECT CONCAT('$', CAST(price AS CHAR)) FROM products;

Date Conversions

SELECT CAST('2025-01-15' AS DATE);

SELECT CAST(order_date AS CHAR) FROM orders;

CONVERT (MySQL)

SELECT CONVERT(price, CHAR) FROM products;

SELECT CONVERT('123', SIGNED INTEGER);

CONVERT with Format (SQL Server)

SELECT CONVERT(VARCHAR, order_date, 101) FROM orders; -- MM/DD/YYYY

Safe Casting

SELECT

CASE

WHEN value REGEXP '^[0-9]+$' THEN CAST(value AS INT)

ELSE 0

END as numeric_value

FROM data;

Generate Type Conversions

AI2sql handles data type conversions correctly.