SQL Dates

How Databases Store Dates

Dates and times are stored in dedicated column types rather than as plain text. Storing them this way lets the database compare them correctly, sort them in true chronological order, and calculate the distance between two points in time. If you store a date as a string instead, sorting and comparison can go wrong because '2024-1-9' and '2024-10-1' do not order the way you expect as text.

The most common types are shown below. The exact set and their names vary a little between database systems, but the ideas are consistent.

Filtering by Date

Because dates are true values, you can compare them with the usual operators and with BETWEEN. The query below selects orders placed on a single day.

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate = '2026-07-15';

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '2026-01-01' AND '2026-03-31';

Common Date and Time Functions

Every database provides functions to fetch the current moment, extract parts of a date, and do date arithmetic. Names differ by dialect, so check your system's reference for exact spellings.

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate >= DATE_ADD(CURDATE(), INTERVAL -30 DAY);

Tips for Working with Dates

• Store dates in a date or datetime type, never as free text.
• Prefer the ISO 'YYYY-MM-DD' format for literals to stay portable.
• Store and compare timestamps in UTC when your application spans time zones, and convert for display only.
• Be careful mixing DATE and DATETIME values, since a hidden time component can quietly exclude rows from a filter.