employees.hire_date has been a DATE column since Module 7, and Module 9 covered exactly what DATE stores on each database. None of that matters yet without the functions to actually use it, HR wants to know how long someone's been at Beacon Robotics, which employees were hired this year, and a report with dates formatted for humans, not raw ISO strings.
Learning Objectives
- Get the current date or date and time correctly, on every database in this course.
- Extract a part of a date (year, month, day) and compute the difference between two dates.
- Format a date as a display string, and know the one DATEDIFF trap that catches almost everyone.
Stored Date
hire_date
Part, Difference, or Format?
What do you actually need?
Right Function
The real keyword, per database
Usable Result
A number, a part, or a display string
Getting the Current Date
| Database | Current Date | Current Date and Time |
|---|---|---|
| MySQL / MariaDB | CURDATE() or CURRENT_DATE | NOW() or CURRENT_TIMESTAMP |
| PostgreSQL | CURRENT_DATE | NOW() or CURRENT_TIMESTAMP |
| SQL Server | CAST(GETDATE() AS DATE) | GETDATE() |
| Oracle | CURRENT_DATE (session time zone) or TRUNC(SYSDATE) | SYSDATE or CURRENT_TIMESTAMP |
| SQLite | CURRENT_DATE or date('now') | CURRENT_TIMESTAMP or datetime('now') |
SQL Server is still the odd one out here, exactly as the Data Types module covered, there's no CURRENT_DATE at all, GETDATE() returns a full date and time that has to be cast down. SQLite's CURRENT_DATE and date('now') both return the date in UTC, not your local time zone, a real and easy-to-miss difference from the other four.
Extracting a Part of a Date
| Database | Year / Month / Day of hire_date |
|---|---|
| MySQL / MariaDB | YEAR(hire_date), MONTH(hire_date), DAY(hire_date) |
| PostgreSQL / Oracle | EXTRACT(YEAR FROM hire_date), EXTRACT(MONTH FROM hire_date), EXTRACT(DAY FROM hire_date) |
| SQL Server | YEAR(hire_date), MONTH(hire_date), DAY(hire_date), or DATEPART(year, hire_date) |
| SQLite | strftime('%Y', hire_date), strftime('%m', hire_date), strftime('%d', hire_date) |
SQLite is the real outlier: there's no YEAR()/MONTH()/EXTRACT() at all, every part comes out of strftime() as a zero-padded text string ('2024', not 2024), worth remembering if the result is going to be compared as a number.
Why WHERE YEAR(hire_date) = 2024 Is a Trap
Wrapping a column in a function, on any of these five databases, usually prevents the database from using an index on that column at all, it has to compute YEAR(hire_date) for every single row before it can even check the condition. The fix is a plain range comparison instead, which can still use an index normally:
SELECT * FROM employees WHERE hire_date >= '2024-01-01' AND hire_date < '2025-01-01';
Adding or Subtracting Time
| Database | 90 Days After hire_date |
|---|---|
| MySQL / MariaDB | DATE_ADD(hire_date, INTERVAL 90 DAY) |
| PostgreSQL | hire_date + INTERVAL '90 days' |
| SQL Server | DATEADD(day, 90, hire_date) |
| Oracle | hire_date + 90 (DATE arithmetic treats whole numbers as days) |
| SQLite | date(hire_date, '+90 days') |
The Difference Between Two Dates
This is the single most common date mistake across these six databases: MySQL and SQL Server both have a function literally named DATEDIFF, and they don't work the same way at all.
| Database | Days Between hire_date and Today |
|---|---|
| MySQL / MariaDB | DATEDIFF(CURDATE(), hire_date) — exactly 2 arguments, always in days |
| PostgreSQL | CURRENT_DATE - hire_date — plain subtraction, returns an integer number of days |
| SQL Server | DATEDIFF(day, hire_date, GETDATE()) — exactly 3 arguments, the unit comes first |
| Oracle | SYSDATE - hire_date — plain subtraction, returns a NUMBER of days |
| SQLite | julianday('now') - julianday(hire_date) |
Calling SQL Server's DATEDIFF with MySQL's 2-argument form, or MySQL's DATEDIFF with SQL Server's 3-argument form, fails immediately on both, same function name, genuinely different signature and argument order. Always check which database a DATEDIFF call is actually running on before trusting it.
Formatting a Date for Display
| Database | hire_date as 'DD Mon YYYY' |
|---|---|
| MySQL / MariaDB | DATE_FORMAT(hire_date, '%d %b %Y') |
| PostgreSQL / Oracle | TO_CHAR(hire_date, 'DD Mon YYYY') |
| SQL Server | FORMAT(hire_date, 'dd MMM yyyy') |
| SQLite | strftime('%d %m %Y', hire_date) — no built-in month-name formatting |
Try It Yourself
Write a query returning every Beacon Robotics employee hired in 2023, using a range comparison, not YEAR(hire_date), on PostgreSQL.
SELECT * FROM employees WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';
Frequently Asked Questions
Does CURRENT_DATE return the server's time zone or the session's?
It varies: PostgreSQL and Oracle's CURRENT_DATE respect the current session's time zone setting, while SQLite's CURRENT_DATE always returns UTC regardless of any session setting. For anything time-zone-sensitive, always confirm which behavior a specific database actually gives rather than assuming.
Why would subtracting two dates give a plain number on PostgreSQL but not somewhere else?
PostgreSQL (and Oracle) treat DATE subtraction as pure day-count arithmetic, hire_date - '2020-01-01' is just an integer. MySQL and SQLite instead require a dedicated function, DATEDIFF() or julianday() subtraction, plain hire_date - '2020-01-01' doesn't do what you'd expect on either.
Common Beginner Mistakes
Wrapping a date column in a function inside WHERE
WHERE YEAR(hire_date) = 2024 usually blocks the database from using an index on hire_date, forcing it to compute YEAR() for every row in the table. WHERE hire_date >= '2024-01-01' AND hire_date < '2025-01-01' returns the same rows and can still use the index normally.
Assuming MySQL's and SQL Server's DATEDIFF take the same arguments
They don't, MySQL's DATEDIFF(date1, date2) takes exactly 2 arguments and always returns days. SQL Server's DATEDIFF(datepart, startdate, enddate) takes 3, with the unit first, and can return days, months, years, or other units depending on that first argument. Mixing the two up is one of the most common cross-database SQL mistakes.
Best Practices
- Filter date ranges with a plain comparison (>=, <) instead of wrapping the column in a function.
- Double-check DATEDIFF's exact signature for the specific database in use, never assume it matches a different database's version of the same function name.
- Format dates for display only at the very end, in the SELECT list or the application layer, never inside a WHERE clause.
Interview Questions
Explain the real difference between MySQL's DATEDIFF and SQL Server's DATEDIFF, with code for both.
MySQL: DATEDIFF('2024-06-01', '2024-01-01'); takes exactly 2 date arguments and always returns the difference in days (152 here). SQL Server: DATEDIFF(day, '2024-01-01', '2024-06-01'); takes 3 arguments, a unit first (day, month, year, and others), then start and end dates, and returns the difference in whatever unit was requested. Same function name, genuinely different signature, a very common source of bugs when code is ported between the two.
Why can WHERE YEAR(hire_date) = 2024 be a performance problem, and what's the fix?
Applying a function to an indexed column generally prevents the database's query planner from using an index on that column, it has to evaluate YEAR(hire_date) for every row in the table before the comparison can even happen, turning an index lookup into a full table scan. The fix is a range comparison on the raw column instead, WHERE hire_date >= '2024-01-01' AND hire_date < '2025-01-01', which returns identical rows and lets the index work normally.
Summary
Getting the current date, extracting a part, computing a difference, and formatting for display are the four real jobs a date column needs to support, and all four have a genuinely different keyword on at least one of the six databases in this course. DATEDIFF is the single biggest trap, MySQL and SQL Server share the name but not the signature.
What's Next?
SQL Views is next: saving a query, like the employee-department JOIN this course has written many times by now, as something that can be queried like a table of its own.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →