SQL CAST and CONVERT: Types, Precision and Safe Conversion

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

CAST(expression AS target_type) is the shared standard-style form. Casting amount to DECIMAL(12,2) states a precision and scale requirement in server databases. Casting to a character type creates text for display or export rather than a numeric measure. The type controls later operations: sorting text '100' and '20' is different from sorting numeric one hundred and twenty. Convert to the intended type before applying the arithmetic or ordering rule that relies on it.

Precision is the total permitted number of decimal digits, while scale describes digits after the decimal point for common DECIMAL definitions. A narrower target can round, truncate or reject a value according to engine rules and context. Converting a large identifier to a small integer type can overflow. Choose a target that reflects the application range rather than merely fitting the handful of values in a sample table.

Implicit conversion occurs when the engine reconciles mixed types automatically. It can make a query appear convenient but conceal errors or prevent efficient index access if the column is converted for every comparison. Match parameter types to their target columns and use explicit conversion where the intended result is otherwise unclear. Conversion of empty text, malformed numeric text or regional dates is not portable; one engine can fail while another returns a coerced value.

SQL Server CONVERT(type,expression,style) includes date and other formatting styles. MySQL CONVERT(expression,type) has a different argument order and also supports a character-set form. PostgreSQL does not offer the same general CONVERT signature, and SQLite relies on CAST with affinity-based behaviour. SQL Server TRY_CAST and TRY_CONVERT return NULL for many failed permitted conversions, but they are not universal functions. A missing converted value may mean invalid input, original NULL, or an intentional fallback, so retain the raw field and an error flag during import validation.

CAST is available in all four systems, but supported type names and failed-conversion rules differ. SQLite CAST uses its conversion and affinity rules and does not enforce fixed decimal precision in DECIMAL(12,2). SQL Server CONVERT has style codes; MySQL CONVERT has distinct type or charset forms. Do not present CONVERT as one portable function.

Check the engine-specific syntax and version requirements in SQL Server CAST and CONVERT.

Calculate a fractional amount divided by four using an explicit decimal cast. Return only sale 106.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT id,CAST(amount AS DECIMAL(12,2))/4.0 AS quarter
FROM sales WHERE id=106;

id | quarter
106 | 1.25

The amount five is converted to the declared numeric target, and the decimal denominator preserves the fractional intent. The resulting value is 1.25. This example separates conversion from final display; it does not update the table or guarantee a particular number of printed trailing zeros.

On a disposable expression, compare conversion of '12' with conversion of 'not-a-number' in the engine you use. Record whether the second fails or coerces, rather than copying another dialect’s result. Test a fractional value against both an integer and a decimal target to see information loss. Keep SQL Server TRY_CONVERT experiments explicitly labelled as T-SQL.