SQL Date Functions: The Complete Guide With Examples

Working with dates is one of the most common—and most error-prone—parts of writing SQL. Every database engine ships its own set of SQL date functions for getting the current time, adding and subtracting intervals, extracting parts of a date, formatting output, and generating sequences of dates. This guide covers the functions you will actually use day to day, with examples for SQL Server, MySQL, PostgreSQL, and Oracle, plus a practical recipe for building a date series.

Getting the current date and time

The starting point for most date logic is “what time is it right now?” Each engine spells this differently:

Tip: store timestamps in UTC and convert on the way out. In PostgreSQL use timestamptz; in SQL Server use datetimeoffset when you need timezone awareness.

Adding and subtracting time with DATEADD

To shift a date forward or backward, use the engine’s interval-arithmetic function.

• SQL Server: DATEADD(day, 7, @d) adds 7 days; use DATEADD(month, -1, @d) to go back a month.
• MySQL: DATE_ADD(d, INTERVAL 7 DAY) or the shorthand d + INTERVAL 7 DAY.
• PostgreSQL: plain interval math—d + INTERVAL '7 days'.
• Oracle: add days directly (d + 7) or use ADD_MONTHS(d, -1) for month math.

Measuring the gap between two dates with DATEDIFF

To find how far apart two dates are:

• SQL Server: DATEDIFF(day, start_date, end_date) returns whole days (swap day for month, year, hour, etc.).
• MySQL: DATEDIFF(end_date, start_date) for days, or TIMESTAMPDIFF(MONTH, start_date, end_date) for other units.
• PostgreSQL: subtract directly—end_date - start_date returns an interval or integer number of days.
• Oracle: subtract dates for days, or use MONTHS_BETWEEN(end_date, start_date).

Extracting parts of a date

Pulling the year, month, day, or weekday out of a date is essential for grouping and reporting.

• Standard SQL / PostgreSQL / MySQL / Oracle: EXTRACT(YEAR FROM d), EXTRACT(MONTH FROM d), EXTRACT(DOW FROM d) (day of week).
• SQL Server: DATEPART(year, d), DATEPART(month, d), DATENAME(weekday, d) for the name.

A common reporting pattern is grouping sales by month: GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date).

Formatting dates for output

Formatting should usually happen in the application layer, but when you need it in SQL:

• SQL Server: FORMAT(d, 'yyyy-MM-dd') or CONVERT(varchar, d, 23).
• MySQL: DATE_FORMAT(d, '%Y-%m-%d').
• PostgreSQL / Oracle: TO_CHAR(d, 'YYYY-MM-DD').

Generating a series of dates

Reports and dashboards often need a continuous run of dates—including days with no data—so charts do not have gaps. Here is how to generate a date sequence in each engine.

PostgreSQL makes it trivial with generate_series:

SELECT d::date FROM generate_series('2026-01-01', '2026-01-31', INTERVAL '1 day') AS g(d);

SQL Server uses a recursive CTE:

WITH dates AS (SELECT CAST('2026-01-01' AS date) AS d UNION ALL SELECT DATEADD(day, 1, d) FROM dates WHERE d < '2026-01-31') SELECT d FROM dates OPTION (MAXRECURSION 0);

MySQL 8+ also uses a recursive CTE with DATE_ADD. In all three, joining this generated calendar against your fact table with a LEFT JOIN gives you zero-filled rows for missing dates—exactly what a time-series dashboard needs.

Common date-handling mistakes to avoid

• Comparing a DATE column to a DATETIME range incorrectly. To capture a full day, use d >= '2026-01-01' AND d < '2026-01-02' rather than BETWEEN with a truncated end.
• Wrapping a column in a function in the WHERE clause. WHERE YEAR(order_date) = 2026 prevents index use; prefer WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'.
• Ignoring time zones. Mixing local and UTC timestamps is a classic source of off-by-one-day bugs.

Frequently Asked Questions

What is the difference between DATEADD and DATEDIFF?

DATEADD shifts a date by a given interval and returns a new date. DATEDIFF takes two dates and returns the number of intervals (days, months, years) between them. One produces a date; the other produces a number.

How do I get just the date from a datetime in SQL?

Cast it: CAST(column AS date) works in SQL Server and PostgreSQL, DATE(column) in MySQL, and TRUNC(column) in Oracle.

Which SQL date function gives the current date?

GETDATE() in SQL Server, NOW() in MySQL and PostgreSQL, and SYSDATE in Oracle. For date only, use CURDATE() (MySQL) or CURRENT_DATE (PostgreSQL).

How do I generate a range of dates in SQL?

Use generate_series() in PostgreSQL, or a recursive common table expression (CTE) with DATEADD/DATE_ADD in SQL Server and MySQL, then left-join it to your data to fill gaps.

Related Articles

• SQL vs NoSQL: How to Choose for Your Workload
• Database Connection Pooling Best Practices

Charlie Frank

Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.