AI Hints
Homeโ€บSQL Tutorialโ€บWorking with Dates
SQL ยท Lesson 27 of 126

How Dates Are Stored

Year, month, day.

โฑ 2 min readโ–ถ Video at 37:38๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Almost every business question has a date in it. Sales this month. Customers who joined last year. Orders that are late. So it's worth taking a little time to understand how dates work in SQL.

How Dates Are Stored
From the video: How Dates Are Stored
  • Sales this month
  • Customers from last year
  • Late orders

The safe date format

In this database, every date is written the same way. Four digits for the year, then a dash, two digits for the month, a dash, and two digits for the day. This is an international standard, and it has a lovely property. If you sort these dates as plain text, they come out in the right order. That's why so many databases use it.

2025-12-31year-month-day
Always4 digits, 2 digits, 2 digits
Sorts correctlyeven as text
StandardISO 8601

Compare dates like text

Because of that format, you can compare dates with the signs you already know. Here we find orders on or after Christmas Eve, twenty twenty five, in date order. Notice the date goes in single quotes. Run it. The last orders of the year, from the twenty fourth onwards.

query.sqlsql
SELECT order_id, order_date, status
FROM orders
WHERE order_date >= '2025-12-24'
ORDER BY order_date
LIMIT 5;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ order_id โ”‚ order_date โ”‚  status   โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 1250     โ”‚ 2025-12-24 โ”‚ delivered โ”‚
โ”‚ 2567     โ”‚ 2025-12-24 โ”‚ delivered โ”‚
โ”‚ 1186     โ”‚ 2025-12-25 โ”‚ delivered โ”‚
โ”‚ 1580     โ”‚ 2025-12-25 โ”‚ delivered โ”‚
โ”‚ 1900     โ”‚ 2025-12-25 โ”‚ shipped   โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

A common mistake

A very common mistake is writing dates in other formats, like thirty one, slash, twelve, slash, twenty twenty five. Those don't sort correctly, and comparisons give wrong answers. If you ever build your own tables, always store dates as year, month, day.

31/12/2025sorts wrongly
12/31/2025also sorts wrongly
2025-12-31always correct
Rulestore year-month-day

Summary

Dates here are year, month, day, and they compare and sort just like text. Next, pulling out the year or the month.

Key takeaways
  • YYYY-MM-DD
  • Compare with < > BETWEEN
  • Dates go in quotes
Next up: Years and Months โ€” strftime