SQL

Date Data Types

Date Data Types in SQL

📅 Date Data Types are used to store calendar dates, times, timestamps, and time intervals in a database. They allow SQL databases to accurately record and manipulate temporal information such as birthdays, order dates, event schedules, and transaction timestamps.

📖 What are Date Data Types?

SQL provides several data types for storing date and time values. The available types and their precision vary between database systems, but most relational databases support separate types for dates, times, and combined date-time values.

Information

Choosing the appropriate date data type improves storage efficiency, data accuracy, and query performance.

🎯 Why Use Date Data Types?

Date and time data types make it easy to store, compare, sort, and calculate temporal values.

📌 Store calendar dates accurately.
📌 Record precise timestamps.
📌 Perform date and time calculations.
📌 Filter and sort records by date.
📌 Support scheduling, reporting, and auditing.

📚 Common Date Data Types

Data Type	Description	Example Value
DATE	Stores only the calendar date.	2026-08-02
TIME	Stores only the time of day.	14:30:45
DATETIME	Stores both date and time.	2026-08-02 14:30:45
TIMESTAMP	Stores a date-time value, often used for recording events. Behavior varies by database.	2026-08-02 14:30:45
YEAR	Stores a year value (supported by some databases).	2026

📝 Creating a Table with Date Columns

Create Table with Date Data Types

CREATE TABLE Events (
    EventID INT PRIMARY KEY,
    EventName VARCHAR(100),
    EventDate DATE,
    StartTime TIME,
    CreatedAt DATETIME
);

➕ Insert Date Values

Insert Date and Time Values

INSERT INTO Events
(EventID, EventName, EventDate, StartTime, CreatedAt)
VALUES
(1, 'SQL Workshop', '2026-08-15', '10:00:00', '2026-08-01 09:30:00');

Tip

The ISO 8601 format ( YYYY-MM-DD for dates and YYYY-MM-DD HH:MM:SS for date-time values) is widely supported and recommended because it avoids ambiguity.

🔍 Retrieve Date Values

Select Date Columns

SELECT
    EventName,
    EventDate,
    StartTime,
    CreatedAt
FROM Events;

📊 Example Table

EventID	EventName	EventDate	StartTime	CreatedAt
1	SQL Workshop	2026-08-15	10:00:00	2026-08-01 09:30:00
2	Annual Meeting	2026-09-10	14:00:00	2026-08-05 11:15:00

🔎 Filter Records by Date

Find Events After a Date

SELECT *
FROM Events
WHERE EventDate >= '2026-09-01';

📅 Sort by Date

Order by Date

SELECT *
FROM Events
ORDER BY EventDate ASC;

⏰ Common Date Functions

Function	Purpose
CURRENT_DATE	Returns the current date (or database-specific equivalent).
CURRENT_TIME	Returns the current time.
CURRENT_TIMESTAMP	Returns the current date and time.
Date Arithmetic	Add or subtract days, months, or years using database-specific functions.

Current Date and Time

SELECT
    CURRENT_DATE,
    CURRENT_TIME,
    CURRENT_TIMESTAMP;

⚖️ DATE vs DATETIME vs TIMESTAMP

Feature	DATE	DATETIME	TIMESTAMP
Stores Date	✅ Yes	✅ Yes	✅ Yes
Stores Time	❌ No	✅ Yes	✅ Yes
Typical Use	Birthdays, holidays.	Appointments, schedules.	Audit logs, record creation times.
Behavior	Date only.	Date and time.	Implementation varies by database.

💼 Real-World Examples

🎂 Store customer birthdays using DATE.
🛒 Record order creation time using DATETIME or TIMESTAMP.
📅 Schedule appointments with date and time values.
🏥 Track patient admission dates.
📦 Record shipment and delivery dates.

🗄️ Database Compatibility

Database System	Date Data Type Support
MySQL	Supports DATE, TIME, DATETIME, TIMESTAMP, and YEAR.
PostgreSQL	Supports DATE, TIME, TIMESTAMP, and time zone-aware variants.
SQL Server	Supports DATE, TIME, DATETIME, DATETIME2, and related types.
Oracle	Supports DATE, TIMESTAMP, and timestamp variants with time zones.
SQLite	No dedicated date/time data types; date and time values are commonly stored as text, integers, or real numbers and processed using built-in date functions.

⚠️ Common Mistakes

❌ Storing dates as plain text instead of using date data types.
❌ Using ambiguous date formats such as 01/02/2026.
❌ Choosing DATETIME when only a date is required.
❌ Assuming TIMESTAMP behaves identically across all database systems.

Warning

Date and time behavior, precision, valid ranges, and time zone handling differ among database systems. Always verify how your specific DBMS stores and processes temporal values.

⚠️ Best Practices

Best Practice

Use the most appropriate date data type for the information you need to store, prefer ISO 8601 date formats, store dates in native date/time columns instead of strings, consider time zone requirements for global applications, and use built-in date functions for calculations instead of manual string operations.

🚀 Key Points to Remember

📌 SQL provides specialized data types for dates and times.
📌 Use DATE when only the calendar date is needed.
📌 Use TIME for time-only values.
📌 Use DATETIME or TIMESTAMP when both date and time are required.
📌 Prefer ISO 8601 date formats for portability and clarity.
📌 Date and time capabilities vary between database systems.

>>"Choosing the correct date data type today prevents countless data and reporting problems tomorrow."

Summary

✅ Date data types allow SQL databases to store and manage calendar dates, times, and timestamps efficiently. By selecting the appropriate type, following standard date formats, and understanding database-specific behavior, you can build applications that handle temporal data accurately, consistently, and efficiently.