Core Code
Core Code Modern Dev Handbook

SQL Dates

Learn how to store and work with dates and times using DATE, DATETIME, and TIMESTAMP, and how to filter rows by date ranges.
On this page

SQL Dates

Dates and times are common in databases: orders, logins, events, and created timestamps. SQL provides multiple data types and functions to store and query date/time values.

Common Date and Time Data Types

  • DATE – date only (YYYY-MM-DD)
  • DATETIME – date and time (YYYY-MM-DD HH:MM:SS)
  • TIMESTAMP – date and time, often used for tracking changes (behavior depends on database)

Create a Table with Dates

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Insert Date Values

INSERT INTO orders (customer_id, order_date)
VALUES (1, '2026-02-13');

Filter by Exact Date

SELECT *
FROM orders
WHERE order_date = '2026-02-13';

Filter by Date Range

BETWEEN is inclusive. It works well for DATE columns.

SELECT *
FROM orders
WHERE order_date BETWEEN '2026-02-01' AND '2026-02-28';

Filtering DATETIME Safely

When working with DATETIME values, using an exclusive end boundary is often safer.

SELECT *
FROM orders
WHERE created_at >= '2026-02-01 00:00:00'
  AND created_at <  '2026-03-01 00:00:00';

Get Today's Date (MySQL / MariaDB)

SELECT CURDATE();

Get Current Date and Time

SELECT NOW();

Extract Parts of a Date

SELECT YEAR(order_date)  AS order_year,
       MONTH(order_date) AS order_month,
       DAY(order_date)   AS order_day
FROM orders;

Common Mistakes

  • Comparing DATETIME values without considering time
  • Using BETWEEN on DATETIME with an end date that misses late-day records
  • Storing dates as text instead of DATE/DATETIME

Best Practice

Use DATE for date-only fields and DATETIME/TIMESTAMP for exact timestamps. Index date columns used in range filters to improve performance.

Next Step

Continue with SQL Views to learn how to create reusable virtual tables.

SQL Views →