Introduction
Nearly every real-world database has date or timestamp columns: order dates, signup dates, log timestamps. SQL provides functions to get the current date/time, add or subtract intervals, calculate the difference between two dates, and extract individual parts like year or month. These functions are essential for reports such as 'orders placed in the last 30 days' or 'signups grouped by month.'
Cricket analogy: Scorecards timestamp every ball bowled, and analysts use date functions to pull 'wickets taken in the last 30 days' or group a bowler's economy rate by month across a long season.
Syntax
SELECT CURRENT_DATE AS today,
NOW() AS current_timestamp_value,
DATE_ADD(date_column, INTERVAL 7 DAY) AS a_week_later,
DATE_SUB(date_column, INTERVAL 1 MONTH) AS a_month_earlier,
DATEDIFF(date_column1, date_column2) AS day_difference,
EXTRACT(YEAR FROM date_column) AS year_part,
EXTRACT(MONTH FROM date_column) AS month_part
FROM table_name;Explanation
CURRENT_DATE returns today's date and NOW() (or CURRENT_TIMESTAMP) returns the current date and time. DATE_ADD and DATE_SUB shift a date forward or backward by a given interval such as days, months, or years; syntax varies by vendor (PostgreSQL uses date_column + INTERVAL '7 days'). DATEDIFF calculates the number of days (or the relevant unit) between two dates, though argument order differs across databases. EXTRACT pulls out a specific component of a date, such as YEAR, MONTH, DAY, or HOUR, which is commonly used with GROUP BY to summarize data by time period.
Cricket analogy: CURRENT_DATE flags today's fixture while NOW() timestamps the exact ball bowled; DATE_ADD schedules the next Test five days out, DATEDIFF counts days since the last century, and EXTRACT(MONTH) groups a batsman's runs by month.
Example
-- orders table
-- order_id | order_date | ship_date
-- 1 | 2026-06-01 | 2026-06-05
-- 2 | 2026-06-15 | 2026-06-16
-- 3 | 2026-07-01 | NULL
SELECT order_id,
order_date,
DATEDIFF(ship_date, order_date) AS days_to_ship,
EXTRACT(MONTH FROM order_date) AS order_month,
DATE_ADD(order_date, INTERVAL 30 DAY) AS follow_up_date
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '60' DAY;Output
For order_id 1: days_to_ship = 4, order_month = 6, follow_up_date = 2026-07-01. For order_id 2: days_to_ship = 1, order_month = 6, follow_up_date = 2026-07-15. For order_id 3: days_to_ship = NULL (because ship_date is NULL and DATEDIFF cannot compute a difference against NULL), order_month = 7, follow_up_date = 2026-07-31. The WHERE clause restricts results to orders placed within the last 60 days from today.
Cricket analogy: For match 1, days_to_result = 4 and follow-up review is scheduled 2026-07-01; for the rain-abandoned match, days_to_result is NULL because DATEDIFF can't compare against a missing result date, and the report only lists fixtures from the last 60 days.
Key Takeaways
- CURRENT_DATE returns today's date; NOW()/CURRENT_TIMESTAMP returns the current date and time.
- DATE_ADD and DATE_SUB shift a date by a specified interval like days, months, or years.
- DATEDIFF computes the difference between two dates, but argument order varies by database vendor.
- EXTRACT pulls a specific component (YEAR, MONTH, DAY) out of a date, often paired with GROUP BY.
- Any date function applied to a NULL date value returns NULL, so watch for missing timestamps.
Practice what you learned
1. Which function returns only the current date without a time component?
2. If order_date = '2026-05-01', what does DATE_ADD(order_date, INTERVAL 10 DAY) return?
3. What is a common use of the EXTRACT function combined with GROUP BY?
4. What does DATEDIFF(ship_date, order_date) return if ship_date is NULL?
Was this page helpful?
You May Also Like
String Functions in SQL
Manipulate text data using functions like CONCAT, SUBSTRING, UPPER, LOWER, TRIM, LENGTH, and REPLACE.
Aggregate Functions in SQL
Learn how COUNT, SUM, AVG, MIN, and MAX summarize sets of rows into single values, and how each handles NULLs.
GROUP BY and HAVING
Understand how GROUP BY partitions rows into groups and how HAVING filters those groups after aggregation, unlike WHERE.