Data Types And Casting

Every value stored in a SQL database has a data type. Whether you are working with a customer's name, an account balance, a signup date, or a simple true/false flag, the database engine needs to know exactly what kind of data it is dealing with so it can store it efficiently, enforce constraints, and apply the right operations.

Understanding data types matters in practice because mismatches between types are one of the most common sources of subtle bugs — queries that silently return wrong results, comparisons that never match, or inserts that truncate data without warning. This lesson walks through the core SQL data types, how databases convert between them automatically, and how to convert them explicitly with CAST and type-specific functions.

The Core Data Type Categories

SQL data types fall into a few broad categories. Most databases implement all of these, though the specific type names and storage limits vary by vendor (PostgreSQL, MySQL, SQLite, SQL Server each have their own flavors).

Text types store character data:

• CHAR(n) — fixed-length string, always padded to n characters
• VARCHAR(n) — variable-length string up to n characters
• TEXT — variable-length string with no practical limit

Numeric types store numbers:

• INTEGER / INT — whole numbers (typically 32-bit)
• BIGINT — large whole numbers (64-bit)
• DECIMAL(p, s) / NUMERIC(p, s) — exact decimal numbers with p total digits and s digits after the decimal point; use this for money
• FLOAT / REAL / DOUBLE — approximate floating-point numbers; avoid for money

Date and time types:

• DATE — calendar date only (year, month, day)
• TIME — time of day only
• TIMESTAMP — date and time combined

Boolean type:

• BOOLEAN — true or false (stored as 1/0 in many databases)

-- Explore how different literal values are typed
SELECT
  42                        AS integer_value,
  3.14                      AS decimal_value,
  'hello'                   AS text_value,
  TRUE                      AS boolean_value,
  '2025-06-15'              AS date_string,
  TYPEOF(42)                AS int_type,
  TYPEOF(3.14)              AS float_type,
  TYPEOF('hello')           AS text_type;

Note: TYPEOF() is a SQLite-specific function. Other databases have equivalents like pg_typeof() in PostgreSQL or SQL_VARIANT_PROPERTY() in SQL Server.

Implicit Type Conversion

Databases often convert types automatically when the context makes the intended type clear. This is called implicit conversion or type coercion. While convenient, it can lead to unexpected behavior if you are not aware it is happening.

A common example is comparing a number stored as text with an integer. Some databases will coerce the text to a number and the comparison will work; others will coerce the integer to text and produce alphabetical ordering instead of numeric ordering.

-- Create a small table to demonstrate implicit conversion
CREATE TABLE IF NOT EXISTS orders (
  id       INTEGER,
  amount   TEXT,   -- intentionally stored as text
  quantity INTEGER
);

INSERT OR IGNORE INTO orders VALUES
  (1, '150.00', 3),
  (2, '20.00',  10),
  (3, '95.50',  1),
  (4, '200.00', 5);

-- Sorting as TEXT gives alphabetical order: 150, 20, 200, 95
-- This is a classic implicit conversion bug
SELECT id, amount
FROM orders
ORDER BY amount;

Notice the result: 150.00 comes before 20.00 because text sorting is alphabetical, not numeric. The database happily accepted the ORDER BY without complaint. To fix this you need an explicit cast.

Explicit Casting with CAST

The CAST function converts a value from one type to another explicitly and unambiguously. The syntax is the same across most SQL databases:

CAST(expression AS target_type)

Using CAST in the previous example immediately fixes the ordering problem:

CREATE TABLE IF NOT EXISTS orders (
  id       INTEGER,
  amount   TEXT,
  quantity INTEGER
);

INSERT OR IGNORE INTO orders VALUES
  (1, '150.00', 3),
  (2, '20.00',  10),
  (3, '95.50',  1),
  (4, '200.00', 5);

-- Correct numeric ordering after casting text to a real number
SELECT
  id,
  amount,
  CAST(amount AS REAL) AS amount_numeric,
  quantity,
  CAST(amount AS REAL) * quantity AS total_value
FROM orders
ORDER BY CAST(amount AS REAL);

CAST also lets you control precision when storing or displaying values. Casting a REAL to an INTEGER truncates the decimal portion — it does not round.

Working with Dates as Strings

Date handling is one of the most common places where type awareness pays off. Dates are often imported or received as text strings. Treating them as text means you can concatenate and split them, but you lose the ability to do date arithmetic — calculating the number of days between two events, finding records from the last 30 days, and so on.

CREATE TABLE IF NOT EXISTS signups (
  user_id    INTEGER,
  username   TEXT,
  signed_up  TEXT   -- stored as ISO 8601 text: 'YYYY-MM-DD'
);

INSERT OR IGNORE INTO signups VALUES
  (1, 'alice',   '2025-01-10'),
  (2, 'bob',     '2025-02-28'),
  (3, 'carol',   '2025-03-15'),
  (4, 'dave',    '2024-12-01');

-- Filter signups from 2025 by comparing date strings
-- This works because ISO 8601 format sorts lexicographically
SELECT username, signed_up
FROM signups
WHERE signed_up >= '2025-01-01'
ORDER BY signed_up;

ISO 8601 date strings (YYYY-MM-DD) have the useful property that lexicographic ordering matches chronological ordering, so string comparisons work for date ranges. This is not true for MM/DD/YYYY or other regional formats — those must be converted before filtering or sorting.

Try It Yourself

A product catalog table has been set up with a mix of numeric and text data. Your task is to:

1. Cast price_text to a numeric type so arithmetic works correctly
2. Calculate a discounted price (10% off) for each product
3. Cast the result back to INTEGER (whole dollars only — truncate, not round)
4. Return only products where the discounted whole-dollar price is greater than 50

CREATE TABLE IF NOT EXISTS products (
  id          INTEGER,
  name        TEXT,
  price_text  TEXT,    -- e.g. '79.99'
  category    TEXT
);

INSERT OR IGNORE INTO products VALUES
  (1, 'Wireless Keyboard', '79.99',  'peripherals'),
  (2, 'USB Hub',           '34.50',  'peripherals'),
  (3, 'Monitor Stand',     '55.00',  'accessories'),
  (4, 'Webcam HD',         '129.99', 'peripherals'),
  (5, 'Cable Organizer',   '12.95',  'accessories'),
  (6, 'Laptop Sleeve',     '45.00',  'accessories');

-- Your query here:
-- 1. Cast price_text to REAL
-- 2. Calculate 10% discount: price * 0.9
-- 3. Cast discounted price to INTEGER (truncate)
-- 4. Filter where discounted INTEGER price > 50
SELECT
  name,
  price_text,
  CAST(price_text AS REAL)                          AS price_numeric,
  CAST(price_text AS REAL) * 0.9                   AS discounted_price,
  CAST(CAST(price_text AS REAL) * 0.9 AS INTEGER)  AS discounted_whole_dollars
FROM products
WHERE CAST(CAST(price_text AS REAL) * 0.9 AS INTEGER) > 50
ORDER BY discounted_whole_dollars DESC;

Key Takeaways

• Every SQL value has a data type: text, integer, decimal, boolean, or date/time. Choosing the right type at schema design time prevents entire categories of bugs.
• Use DECIMAL or NUMERIC for money and other values where exactness matters. FLOAT and REAL are approximate and should not be used for financial calculations.
• Implicit type conversion (coercion) happens silently and can produce wrong results — most dangerously with sorting and comparisons on numbers stored as text.
• CAST(value AS type) is the standard, portable way to convert between types explicitly. Prefer it over database-specific shorthand when writing queries that need to run on multiple systems.
• ISO 8601 date strings (YYYY-MM-DD) sort correctly as text, but for date arithmetic you must use your database's native date functions or cast to a date type.
• Casting from REAL to INTEGER truncates (drops the decimal), it does not round. Use ROUND() first if rounding is what you need.

Pro Tip: When importing data from CSV files or external APIs, always validate and cast string columns to their intended types as the first step — either in a staging table or with CAST in your INSERT ... SELECT. Fixing type mismatches at the boundary is far cheaper than hunting down data quality issues buried in downstream queries.

Next Steps

Now that you understand how SQL handles types, the next lesson covers constraints and keys — the rules you define at the schema level to guarantee data integrity and prevent invalid data from ever entering your tables.