SQL Dates

Handling date and time values effectively is a core skill in SQL database design and querying. Because date formats, time zones, and manipulation functions differ across database engines, understanding storage types, literal formats, and query patterns ensures clean data integrity and accurate reporting.

1. Standard Date & Time Data Types

Most relational database engines provide dedicated data types to store calendar dates, times of day, or combined timestamps.

2. Writing Portable Date Literals

To prevent regional interpretation errors (for example, whether 01/02/2026 means January 2nd or February 1st), always use ISO 8601 string formats when writing queries or inserting data:

-- ISO 8601 Date Format (Universal)
'2026-09-27'

-- ISO 8601 Timestamp / DATETIME Format
'2026-09-27 14:30:00'
'2026-09-27T14:30:00Z' -- UTC indicator

3. Getting Current Date & Time Across Engines

Retrieving system timestamps varies depending on the database platform:

4. Querying Date Ranges

When filtering rows by date, filtering full days on columns that contain time components requires careful boundary handling.

The Problem with BETWEEN on Timestamps

If an OrderDate column contains time components (e.g., '2026-09-27 15:45:00'), using BETWEEN with short date strings can omit records:

-- RISKY: Misses any orders placed after 00:00:00 on September 27th!
SELECT * 
FROM Orders 
WHERE OrderDate BETWEEN '2026-09-01' AND '2026-09-27';

Best Practice: Half-Open Interval (>= and <)

Using an inclusive start date (>=) and an exclusive end date (<) guarantees that all timestamps throughout the entire final day are captured cleanly:

-- RECOMMENDED: Captures every timestamp up to 23:59:59.999 on September 27th
SELECT * 
FROM Orders 
WHERE OrderDate >= '2026-09-01'
  AND OrderDate <  '2026-09-28';

Performance Tip: Avoid wrapping table columns in date functions inside the WHERE clause (e.g., WHERE YEAR(OrderDate) = 2026). Doing so makes the query non-sargable, preventing the database optimizer from using indexes on OrderDate.

5. Adding & Subtracting Intervals

Calculating relative dates (e.g., finding orders placed within the last 30 days) uses engine-specific interval syntax:

-- PostgreSQL
SELECT * FROM Orders WHERE OrderDate >= NOW() - INTERVAL '30 days';

-- SQL Server
SELECT * FROM Orders WHERE OrderDate >= DATEADD(day, -30, GETDATE());

-- MySQL
SELECT * FROM Orders WHERE OrderDate >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- Oracle
SELECT * FROM Orders WHERE OrderDate >= SYSDATE - 30;

-- MS Access
SELECT * FROM Orders WHERE OrderDate >= DateAdd("d", -30, Date());

Engine Feature Comparison Matrix