SQL – Working with Dates
Store, compare, and calculate with dates and times in SQL using date functions and formatting.
Table of Contents
Dates and times are everywhere in databases — order dates, signup timestamps, appointment schedules, expiry dates. SQL provides functions for getting the current date, extracting parts (year, month, day), calculating differences, and formatting dates for display.
SQLite stores dates as text (‘YYYY-MM-DD’) or as numeric Unix timestamps, which makes date arithmetic particularly clean with functions like date(), datetime(), and strftime().
Key SQLite date functions
| Function | Example | Returns |
|---|---|---|
| date(‘now’) | date(‘now’) | 2024-04-20 |
| date(‘now’,‘-7 days’) | 7 days ago | |
| strftime(‘%Y’, date) | Year only | |
| julianday(d2)-julianday(d1) | Days between |
Try It Yourself — Interactive SQL Editor
Edit the query below and click Run Query ▶ to see live results powered by SQLite running directly in your browser.
Key Points
- Store dates in ISO 8601 format (YYYY-MM-DD) for correct sorting
- julianday() converts dates to a numeric scale for arithmetic
- strftime() formats dates: %Y=year, %m=month, %d=day, %H=hour
- date(‘now’, ‘-30 days’) calculates relative dates
Pro Tip from CodesCompiler: The best way to learn SQL is to break things intentionally — modify the query above, change the WHERE conditions, try different columns. Every error teaches you something the docs cannot.
In the next lesson, we continue exploring SQL’s powerful feature set to build your database mastery.