7.2 SQL Data Types (Numeric, Character, Date/Time, Special)

Level: 7 | Version: v1.1 | Author: Meptrasoft

Overview

A data type defines what kind of value a column can store.

For example:

Age       → INT
Price     → DECIMAL
Name      → VARCHAR
Added On  → DATE
In Stock  → BOOLEAN

Choosing the appropriate data type helps maintain valid data and makes the database easier to work with.

SQL data types can be broadly grouped into:

Numeric
Character
Date/Time
Special

SQL Data Types: Numeric, Character, Date/Time, and Special Categories — Figure 1: Common SQL data types can be grouped into numeric, character, date/time, and special-purpose types.

1. Numeric Types

Numeric types store whole numbers or decimal values.

Type	Common Use
`INT`	Whole numbers
`BIGINT`	Large whole numbers
`DECIMAL(p,s)` / `NUMERIC(p,s)`	Exact decimal values
`FLOAT` / `DOUBLE`	Approximate numeric values

Example:

Quantity → 10
Price    → 599.99

For values such as money, DECIMAL/NUMERIC is generally preferred when exact decimal precision is required.

2. Character Types

Character types store text.

Type	Common Use
`CHAR(n)`	Fixed-length text
`VARCHAR(n)`	Variable-length text
`TEXT`	Longer text

Examples:

Name        → 'Arjun'
Email       → 'arjun@gmail.com'
Description → 'Wireless mouse'

CHAR → Fixed length
VARCHAR → Variable length

3. Date and Time Types

Date/time types store calendar and time values.

Type	Stores
`DATE`	Date
`TIME`	Time
`TIMESTAMP`	Date and time

Examples:

2026-08-28
10:30:00
2026-08-28 10:30:00

Using proper date/time types is better than storing dates as ordinary text because databases can correctly sort, compare, and calculate with them.

4. Special Types

Some values require special-purpose types.

Type	Common Use
`BOOLEAN`	True/false values
`JSON`	Structured/nested JSON data
Binary types	Images, files, or other binary data

Example:

in_stock → TRUE

metadata → {"color":"black","warranty":1}

Note: Exact type names and support vary between database systems.

Practice Table

Example using PostgreSQL:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price NUMERIC(10,2),
    in_stock BOOLEAN,
    description TEXT,
    added_on DATE,
    metadata JSONB
);

Example row:

INSERT INTO products
(product_id, product_name, price, in_stock, description, added_on, metadata)
VALUES
(
    1,
    'Wireless Mouse',
    599.99,
    TRUE,
    'Ergonomic wireless mouse',
    '2026-08-28',
    '{"color":"black","warranty_years":1}'
);

Here:

product_id   → INT
product_name → VARCHAR
price        → NUMERIC
in_stock     → BOOLEAN
description  → TEXT
added_on     → DATE
metadata     → JSONB

Choosing the Right Data Type

Choose a type based on the kind of value, not just how the value looks.

For example:

Age          → INT
Salary       → NUMERIC
Name         → VARCHAR
Birth Date   → DATE
Is Active    → BOOLEAN

Avoid storing everything as VARCHAR. For example, storing salary as text makes numerical calculations and validation harder.

Use the data type that best represents the actual value.

Common Beginner Mistakes

✗ Mistake	✓ Correct Understanding
Store every value as `VARCHAR`	Choose a type that matches the data
Store dates as text	Use an appropriate date/time type
Use `FLOAT` for every decimal value	Use `NUMERIC`/`DECIMAL` when exact precision matters
`CHAR` and `VARCHAR` are always identical	They have different length semantics and storage behavior
`BOOLEAN` stores any text	It represents a logical true/false value
All SQL databases support exactly the same types	Data types and syntax vary by DBMS

Placement Quick Points

NUMERIC
→ INT / BIGINT / DECIMAL

CHARACTER
→ CHAR / VARCHAR / TEXT

DATE/TIME
→ DATE / TIME / TIMESTAMP

SPECIAL
→ BOOLEAN / JSON / BINARY

A data type defines what kind of value a column can store.
Numeric types store numbers.
Character types store text.
Date/time types store temporal values.
Special types handle values such as Boolean, JSON, and binary data.
Choose data types based on the meaning and requirements of the data.
Exact supported types vary between database systems.

Interview Questions

What is a SQL data type?: A data type defines the kind of value a database column can store.
What is the difference between INT and DECIMAL?: INT stores whole numbers, while DECIMAL/NUMERIC stores exact decimal values.
What is the difference between CHAR and VARCHAR?: CHAR is intended for fixed-length character data, while VARCHAR is for variable-length character data.
Why should dates not usually be stored as VARCHAR?: Proper date/time types allow the database to correctly compare, sort, and perform date calculations.
Which data type is suitable for true/false values?: BOOLEAN, where supported.
Which type is commonly used for exact monetary values?: DECIMAL or NUMERIC.

Practice & Hands-Hands-On Exercises

Using the products table:

Identify the data type of each column.
Insert a new product:

INSERT INTO products (product_id, product_name, price, in_stock, description, added_on, metadata)
VALUES (2, 'USB-C Cable', 299.50, TRUE, 'Durable braided cable', '2026-08-28', '{"length":"2m"}');

Change the product price:

UPDATE products SET price = 349.99 WHERE product_id = 2;

Explain why price uses NUMERIC.
Explain why added_on uses DATE.
Give a suitable data type for:
- Employee age
- Employee name
- Joining date
- Salary
- Active status

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

NUMBER     → NUMERIC
TEXT       → CHARACTER
DATE/TIME  → TEMPORAL
SPECIAL    → BOOLEAN / JSON / BINARY

SQL data types define the kind of values a column stores. Choosing the correct type improves data validity and makes the data easier to query and manage.

End of SQL Data Types (Numeric, Character, Date/Time, Special)