What are Date Functions in SQL?
Learn SQL date functions like CURRENT_DATE, EXTRACT, DATEADD, DATEDIFF and DATE_TRUNC to filter, group and calculate durations directly in your queries.
Expected Interview Answer
Date functions in SQL are built-in functions that let you retrieve, extract, format, compare, and perform arithmetic on date and time values, such as getting the current date, adding intervals, or finding the difference between two dates.
Common examples include CURRENT_DATE/NOW() to get the present moment, EXTRACT or DATEPART to pull out a year, month or day, DATEADD/INTERVAL arithmetic to shift a date forward or back, DATEDIFF to measure elapsed time, and DATE_TRUNC/FORMAT to normalize or display values. Exact names vary by dialect (MySQL, PostgreSQL, SQL Server, Oracle) but the categories are the same, and they let you group by period, filter recent rows, and compute ages or durations directly in the database.
- Filter and group rows by day, month, or year
- Compute ages, tenures, and durations in the query
- Avoid pulling raw dates into application code
- Standardize and format dates for reports
- Enable time-based analytics like month-over-month trends
AI Mentor Explanation
A date function is like the match clock and over counter a scorer uses: it tells you the exact ball and over right now, how many overs have elapsed since the innings began, and how long until the powerplay ends. Just as the scorer computes 'overs remaining' from two timestamps, DATEDIFF and DATEADD compute gaps and future points from stored dates.
Step-by-Step Explanation
Step 1
Get the current moment
Use NOW(), CURRENT_TIMESTAMP, or CURRENT_DATE to capture 'right now' as the anchor for comparisons.
Step 2
Extract parts
Use EXTRACT(YEAR FROM col) or DATEPART to pull the year, month, day, or weekday out of a date.
Step 3
Do date arithmetic
Add or subtract intervals with DATEADD, INTERVAL '1 day', or date + N to shift a date forward or back.
Step 4
Measure differences
Use DATEDIFF or subtract two dates to compute the number of days, months, or years between them.
Step 5
Truncate or format
Use DATE_TRUNC to snap to the start of a period for grouping, and FORMAT/TO_CHAR for display strings.
What Interviewer Expects
- Awareness that function names differ across SQL dialects
- Knowing NOW()/CURRENT_DATE for the current moment
- Using EXTRACT/DATEPART to isolate date parts
- Computing differences with DATEDIFF or date subtraction
- Using DATE_TRUNC or GROUP BY on a period for time-series analysis
Common Mistakes
- Assuming one dialect's function name works everywhere
- Confusing DATEDIFF argument order and getting a negative sign
- Comparing a date to a string without proper casting
- Forgetting that time zones affect NOW() and stored timestamps
- Applying a function to a column in WHERE, defeating an index
Best Answer (HR Friendly)
“Date functions in SQL are ready-made tools for working with dates and times, like getting today's date, pulling out the year, or finding how many days are between two dates. They let the database do time-based calculations directly, so reports and filters like 'orders from last month' are quick and accurate.”
Code Example
SELECT
CURRENT_DATE AS today,
EXTRACT(YEAR FROM order_date) AS order_year,
order_date + INTERVAL '7 days' AS due_date,
(CURRENT_DATE - order_date) AS days_since_order,
DATE_TRUNC('month', order_date) AS order_month
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY order_date DESC;SELECT
DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS orders,
SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;Follow-up Questions
- How do date functions differ between MySQL, PostgreSQL, and SQL Server?
- Why can applying a date function to a column in WHERE hurt performance?
- How do you handle time zones with TIMESTAMP vs TIMESTAMPTZ?
- How would you find all rows from the current month?
- What is the difference between DATE, DATETIME, and TIMESTAMP types?
MCQ Practice
1. Which function returns the current date in standard SQL?
CURRENT_DATE is the SQL-standard function for the current date; some dialects also offer NOW() or GETDATE().
2. Which function is used to pull the year out of a date value?
EXTRACT(YEAR FROM col) (or DATEPART in SQL Server) isolates the year component; string slicing is fragile and dialect-dependent.
3. What does DATEDIFF typically return?
DATEDIFF returns the difference between two dates as a number of a chosen unit, such as days.
Flash Cards
How do you get the current date? — CURRENT_DATE (standard), or NOW()/GETDATE()/SYSDATE depending on the dialect.
How do you extract the month from a date? — EXTRACT(MONTH FROM col) in standard SQL, or DATEPART(month, col) in SQL Server.
How do you add 7 days to a date? — date + INTERVAL '7 days' (PostgreSQL) or DATEADD(day, 7, date) (SQL Server).
How do you group rows by month? — GROUP BY DATE_TRUNC('month', col), which snaps each date to the first of its month.
Why avoid functions on columns in WHERE? — Wrapping an indexed column in a function usually prevents the index from being used, forcing a scan.