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.

What You'll Learn

By the end of this lesson you will be able to look at a CREATE TABLE statement on this engine and say which of its type declarations the database will actually enforce — the answer is fewer than you expect — and write a schema that stores money without losing a cent to floating-point arithmetic. The skill underneath is reading a declared type as a statement of intent rather than a guarantee, and knowing which checks you therefore have to perform yourself.

This lesson is the track's authority on that question: every other lesson that writes DECIMAL(10,2) on a money column is relying on what you settle here. You arrive able to filter, sort and aggregate over columns (Filtering and Sorting, Aggregation and Grouping) and to recognise a missing value for what it is (Null Handling); what is new is that the value a column holds and the type the column was declared with are two separate facts, and a query can depend on either.

The Core Data Type Categories

SQL data types fall into a few broad categories. The type names below are the ones you will meet in most dialects, though the storage limits and the strictness vary sharply by vendor — and SQLite, the engine behind the Run button on this page, is the least strict of the common ones. Read the list first, then read the section after it, which says what this engine really does with each of them.

Text types store character data:

• CHAR(n) — nominally a fixed-length string of n characters
• VARCHAR(n) — nominally a variable-length string of at most 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) — in engines that implement them, a decimal number carrying p total digits of which s follow the point, held without binary rounding error
• FLOAT / REAL / DOUBLE — approximate floating-point numbers

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

-- 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.

What This Engine Actually Enforces: Type Affinity

Here is the fact that reorganises everything above, and the reason this lesson exists before the one on constraints. On SQLite, a column's declared type is an affinity, not a rule. The engine uses the declaration to decide what it would prefer a value to be, and then stores whatever you actually give it. Every length, precision and scale in a declaration is parsed and then ignored.

Run this and read each column against what its declaration promised:

CREATE TABLE declared (
  fixed    CHAR(1),
  short    VARCHAR(5),
  money    DECIMAL(10, 2),
  flag     BOOLEAN,
  day      DATE
);

INSERT INTO declared VALUES ('a', 'abcdefghij', 0.1, 'yes', '2024-13-99');

SELECT
  '[' || fixed || ']'  AS fixed_shown,
  LENGTH(fixed)        AS fixed_len,
  LENGTH(short)        AS short_len,
  TYPEOF(money)        AS money_type,
  TYPEOF(flag)         AS flag_type,
  TYPEOF(day)          AS day_type
FROM declared;

Five promises, five outcomes:

• CHAR(1) does not pad. The value prints as [a] with a length of 1, not padded out to any width.
• VARCHAR(5) accepted ten characters. The length limit is not a limit; nothing was truncated and nothing was reported.
• DECIMAL(10, 2) stores a float. TYPEOF says real, and the two decimal places in the declaration bought nothing.
• BOOLEAN accepted the string 'yes' and stored it as text. There is no boolean type here; TRUE and FALSE are spellings of 1 and 0.
• DATE accepted '2024-13-99' — a thirteenth month and a ninety-ninth day — and stored it as text. Dates are text on this engine, and nothing validates them.

The consequence for money is the one to carry away, and it is the reverse of the usual advice. Because DECIMAL(10,2) is a float here, it carries floating-point error like any other float:

CREATE TABLE line_items (
  id    INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

INSERT INTO line_items VALUES (1, 'Cable', 1.10), (2, 'Sleeve', 2.20);

SELECT
  SUM(price)                  AS invoice_total,
  SUM(price) = 3.30           AS balances,
  printf('%.2f', SUM(price))  AS displayed
FROM line_items;

The total prints as 3.3000000000000003, the equality test against 3.30 returns 0, and only the printf display looks right. A reconciliation built on that column fails on a rounding error nobody wrote.

So the rule for this engine: store money as an INTEGER count of cents. 110 and 220 are exact, they sum to exactly 330, and the comparison to 330 is true. Divide by 100 only at the moment you display it — or better, format with printf('%.2f', cents / 100.0) at the edge and keep every calculation in integers. Other engines do offer a genuinely exact decimal type: PostgreSQL's documentation calls numeric "especially recommended for storing monetary amounts and other quantities where exactness is required" (PostgreSQL 16 documentation: Numeric Types, arbitrary precision numbers). The advice is right there and wrong here, and knowing which engine you are on is the whole of the difference.

If you want the declarations enforced, SQLite offers STRICT tables, which do reject a value whose type does not match the column — but the rest of this lesson, and the rest of this track, uses ordinary tables, because that is what you will meet in existing databases.

CREATE TABLE parts (id INTEGER PRIMARY KEY, code VARCHAR(3), quantity INTEGER);
INSERT INTO parts VALUES (1, 'ABCDE', 'seven');
SELECT code, LENGTH(code) AS code_len, quantity, TYPEOF(quantity) AS quantity_type FROM parts;

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 — and this is where SQLite's rule is genuinely surprising, so it is worth stating exactly rather than by analogy. This engine compares by storage class first. Values are ordered null, then numbers, then text, then blobs, and a text value is therefore greater than any number regardless of what it spells. That produces a pair of results that look contradictory until you know the rule:

SELECT
  ('150.00' > 20)  AS text_beats_number,
  ('10' = 10)      AS text_equals_number,
  (CAST('10' AS INTEGER) = 10) AS cast_then_compare;

'150.00' > 20 is 1, but not because 150 is bigger than 20 — '2' > 20 is also true, and so is '0' > 20. Any text beats any number. And '10' = 10 is 0: they are in different storage classes, so they are not equal, even though they look it. The cure is to cast explicitly, which puts both sides in the same class and makes the comparison mean what you expect.

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

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

-- Sorting as TEXT compares character by character, not by value
SELECT id, amount
FROM orders
ORDER BY amount, id;

Notice the result: 150.00 comes before 20.00, because comparing two text values goes character by character and '1' precedes '2'. 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 orders (
  id       INTEGER,
  amount   TEXT,
  quantity INTEGER
);

INSERT 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), id;

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, so CAST(9.99 AS INTEGER) is 9.

Two Things CAST Will Not Tell You

CAST always produces a value. It has no way to report that the conversion was meaningless, and that leads to two behaviours worth meeting deliberately rather than in production:

SELECT
  CAST('abc' AS INTEGER)    AS not_a_number,
  CAST('12abc' AS INTEGER)  AS leading_digits,
  CAST('' AS INTEGER)       AS empty_string,
  CAST(1.9 AS BANANA)       AS unknown_type,
  TYPEOF(CAST(1.9 AS BANANA)) AS unknown_type_is;

First: a failed conversion returns 0, not NULL and not an error. CAST('abc' AS INTEGER) is 0, and so is the empty string; '12abc' yields 12, because the conversion consumes as many leading numeric characters as it can and silently discards the rest. A column of imported text that contains one 'n/a' therefore contributes a zero to your sum and pulls your average down, with nothing anywhere to indicate it.

Second: the target type name is not checked. CAST(1.9 AS BANANA) runs, and TYPEOF reports real. SQLite applies the same affinity rules to the name you wrote as it does to a column declaration, and a name it does not recognise gets numeric affinity. So a typo like CAST(x AS INTEGR) will not be caught — it simply does something other than what you meant.

The practical habit that follows: when you cast imported text, decide what a non-numeric value should mean before you cast it, and filter or flag those rows explicitly rather than letting them arrive as zeros.

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.

Ordering the Clauses of a Cast-and-Filter Query

Casting imported text usually happens inside a query that also filters and sorts, and the clause order decides which rows are cast at all. The five lines below are one query over this table:

CREATE TABLE imports (id INTEGER PRIMARY KEY, sku TEXT NOT NULL, price_text TEXT NOT NULL);
INSERT INTO imports VALUES (1,'A-1','1099'),(2,'B-2','n/a'),(3,'C-3','2450'),(4,'D-4','899');

CREATE TABLE line_items (id INTEGER PRIMARY KEY, label TEXT NOT NULL, price DECIMAL(10,2) NOT NULL);
INSERT INTO line_items VALUES (1,'Cable',1.10),(2,'Sleeve',2.20);
SELECT SUM(price) AS invoice_total, SUM(price) = 3.30 AS balances FROM line_items;

2 row(s) affected
invoice_total | balances
------------------------
330 | 1

CREATE TABLE readings (id INTEGER PRIMARY KEY, label TEXT NOT NULL, raw TEXT NOT NULL);
INSERT INTO readings VALUES (1,'north','12'),(2,'south','8'),(3,'east','n/a'),(4,'west','15');
SELECT COUNT(*) AS rows_used, SUM(CAST(raw AS INTEGER)) AS total, AVG(CAST(raw AS INTEGER)) AS mean
FROM readings;

4 row(s) affected
rows_used | total | mean
------------------------
3 | 35 | 11.666666666666666

Try It Yourself

A supplier feed arrives with prices as text, in cents, and one product whose price never came through. This is a build task. The starter below runs and lists every product with its raw price string passed straight through, which is not a sale report — the placeholder is still there, no discount has been applied, and the values are text rather than numbers. Work the three TODOs to produce the sale list.

CREATE TABLE products (
id          INTEGER PRIMARY KEY,
name        TEXT NOT NULL,
price_text  TEXT NOT NULL,
category    TEXT NOT NULL
);
INSERT INTO products VALUES
(1, 'Wireless Keyboard', '7999',  'peripherals'),
(2, 'USB Hub',           '3450',  'peripherals'),
(3, 'Monitor Stand',     'n/a',   'accessories'),
(4, 'Webcam HD',         '12999', 'peripherals'),
(5, 'Cable Organizer',   '1295',  'accessories'),
(6, 'Laptop Sleeve',     '4500',  'accessories');
-- TODO 1: exclude the row whose price_text is the placeholder n/a.
--         Do this on the raw text, before any conversion, and say why in your own words.
-- TODO 2: report sale_cents as 90 percent of the price, in whole cents, using integer
--         arithmetic only — no floats anywhere. Wireless Keyboard is 7999 and must give 7199.
-- TODO 3: keep only rows whose sale_cents is above 3000, highest first, ties broken by name.
SELECT name, price_text AS sale_cents
FROM products
ORDER BY name;

6 row(s) affected
name | sale_cents
-----------------
Webcam HD | 11699
Wireless Keyboard | 7199
Laptop Sleeve | 4050
USB Hub | 3105

Before you submit the version that passes, try two variations on it in the block's editor — an unsolved run is never limited, and each one shows you its output beside the expected lines. Predict each before you run it, then put the passing version back and run that last. First, delete the n/a filter and rerun: Monitor Stand does not reappear in the output, because its sale price casts to 0 and 0 is not above 3000 — the guard was invisible here and would not be on a threshold of zero, which is exactly the kind of accident that makes the filter worth writing anyway. Second, replace the integer arithmetic with CAST(price_text AS INTEGER) * 0.9: the numbers acquire decimal tails such as 7199.1 and 11699.1, and the column stops being a count of cents at all — one character is the difference between an exact integer report and a float one.

CREATE TABLE feed (id INTEGER PRIMARY KEY, raw TEXT NOT NULL);
INSERT INTO feed VALUES (1,'120'),(2,'80'),(3,'9');
SELECT id, raw FROM feed WHERE CAST(raw AS INTEGER) > 50 ORDER BY id;
SELECT id, raw FROM feed WHERE raw + 0 > 50 ORDER BY id;
SELECT id, raw FROM feed WHERE raw > 50 ORDER BY id;

Key Takeaways

• On this engine a declared type is an affinity, not a rule: VARCHAR(5) accepts ten characters, CHAR(1) does not pad, BOOLEAN accepts the string 'yes', and DATE accepts '2024-13-99'. Every length and precision in a declaration is parsed and ignored. Use TYPEOF on the data when you need to know what a column really holds.
• Store money as an INTEGER count of cents. DECIMAL(10,2) is a float here, so 1.10 + 2.20 is 3.3000000000000003 and a comparison against 3.30 is false. Other engines differ — PostgreSQL's numeric really is exact — so this is a decision about the engine you are on, not about SQL.
• Comparison is by storage class first: any text value is greater than any number, so '0' > 20 is true and '10' = 10 is false. Cast both sides into the same class before comparing.
• CAST(value AS type) always produces a value and never reports failure: CAST('abc' AS INTEGER) is 0, '12abc' is 12, and an unrecognised target type name is accepted rather than rejected. Decide in a WHERE clause what an unreadable value should mean, rather than letting it arrive as a zero.
• 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.