Home › SQL Tutorial › Date Functions in SQL

Date Functions in SQL

⏱ 9 min read Updated: 27 Oct 2026

SQL date functions help us work with date and time values stored in a database. We can use these functions to get the current date, extract the year or month from a date, calculate date differences, add or subtract days, and format date values.

For example, an employee database may store joining dates, an online shopping website may record order dates, and a payment system may track transaction dates. Date functions make it easier to retrieve and manage this information.

In this article, we will learn about date functions in SQL, their syntax, commonly used functions, and practical examples. If you are new to SQL, you can first go through our SQL Tutorial.

What Are Date Functions in SQL?

Date functions in SQL are built-in functions used to retrieve, extract, compare, format, and perform calculations on date and time values.

The available functions depend on the database management system (DBMS). MySQL, SQL Server, PostgreSQL, and Oracle support many similar operations, but their function names and syntax may differ.

If you want to work with text values instead of dates, read our guide on SQL String Functions.

For example, the following query returns the current date in MySQL:

SELECT CURDATE();

Output:

2026-10-27

The output represents the current date when the query is executed. The actual result changes with the date on which you run the query.

Commonly Used Date Functions in SQL

The following table lists some commonly used date functions and their purposes. The exact syntax depends on the database system.

FunctionPurpose
CURRENT_DATEReturns the current date in supported SQL systems.
NOW()Returns the current date and time in MySQL.
GETDATE()Returns the current date and time in SQL Server.
DATE()Extracts the date portion from a date-time value in MySQL.
YEAR()Extracts the year from a date in MySQL and SQL Server.
MONTH()Returns the month number from a date in MySQL and SQL Server.
DAY()Returns the day of the month in MySQL and SQL Server.
DATE_ADD()Adds an interval to a date in MySQL.
DATE_SUB()Subtracts an interval from a date in MySQL.
DATEDIFF()Calculates the difference between dates; argument order and behavior vary by database.
DATE_FORMAT()Formats a date as a string in MySQL.
EXTRACT()Retrieves a specific part of a date or timestamp in supported databases.

1. CURRENT_DATE() Function in SQL

The CURRENT_DATE function returns the current date without the time portion. In MySQL, you can use CURRENT_DATE or CURRENT_DATE().

Syntax:

SELECT CURRENT_DATE();

Example:

SELECT CURRENT_DATE() AS Today;

Output:

Today
2026-10-27

Explanation: The query returns the current date of the database session. The result does not include hours, minutes, or seconds.

2. NOW() Function in SQL

The NOW() function returns the current date and time in MySQL. It is useful when you need to record when an order was placed, a payment was made, or a record was created.

Syntax:

SELECT NOW();

Example:

SELECT NOW() AS CurrentDateTime;

Sample output:

CurrentDateTime
2026-10-27 10:30:45

Explanation: The output contains both the date and the time. The displayed time is an example; the actual value depends on the database session's time zone and execution time.

Note: In SQL Server, GETDATE() is commonly used for the current date and time. PostgreSQL supports CURRENT_TIMESTAMP and other date-time expressions.

3. DATE() Function in SQL

The DATE() function in MySQL extracts the date portion from a date-time value. It removes the time portion from the returned value.

Syntax:

SELECT DATE(date_time_value);

Example:

SELECT DATE('2026-10-27 10:30:45') AS OrderDate;

Output:

OrderDate
2026-10-27

Explanation: The original value contains both a date and a time. The DATE() function returns only 2026-10-27.

4. YEAR() Function in SQL

The YEAR() function returns the year from a date value. It is useful when we want to group records by year or find records created during a particular year.

Syntax:

SELECT YEAR(date_value);

Example:

SELECT YEAR('2026-10-27') AS OrderYear;

Output:

OrderYear
2026

Explanation: The function extracts 2026 from the supplied date. You can also combine YEAR() with SQL DISTINCT to list each unique year available in a table.

5. MONTH() Function in SQL

The MONTH() function returns the month number from a date. The result ranges from 1 to 12 for valid dates.

Syntax:

SELECT MONTH(date_value);

Example:

SELECT MONTH('2026-10-27') AS OrderMonth;

Output:

OrderMonth
10

Explanation: October is the tenth month of the year, so the function returns 10.

6. DAY() Function in SQL

The DAY() function returns the day of the month from a date value.

Syntax:

SELECT DAY(date_value);

Example:

SELECT DAY('2026-10-27') AS OrderDay;

Output:

OrderDay
27

Explanation: The supplied date is October 27, 2026, so the function returns 27.

7. DATE_ADD() Function in SQL

The DATE_ADD() function in MySQL adds a specified interval to a date. You can use it to calculate future dates, payment deadlines, delivery dates, or subscription expiry dates.

Syntax:

DATE_ADD(date, INTERVAL value unit)

Here, date is the starting date, value specifies the amount to add, and unit defines whether to add days, months, years, or another supported time unit.

Example:

SELECT DATE_ADD('2026-10-27', INTERVAL 7 DAY) AS FutureDate;

Output:

FutureDate
2026-11-03

Explanation: The query adds seven days to October 27, 2026, and returns November 3, 2026.

8. DATE_SUB() Function in SQL

The DATE_SUB() function in MySQL subtracts a specified interval from a date. It is useful for finding past dates, checking recent orders, and calculating earlier deadlines.

Syntax:

DATE_SUB(date, INTERVAL value unit)

Example:

SELECT DATE_SUB('2026-10-27', INTERVAL 10 DAY) AS PastDate;

Output:

PastDate
2026-10-17

Explanation: The query subtracts ten days from October 27, 2026, and returns October 17, 2026.

9. DATEDIFF() Function in SQL

The DATEDIFF() function calculates the difference between two dates. In MySQL, it returns the number of days between the first date and the second date.

Syntax:

DATEDIFF(date1, date2)

Example:

SELECT DATEDIFF('2026-10-27', '2026-10-17') AS DaysDifference;

Output:

DaysDifference
10

Explanation: October 27 is ten days after October 17, so the function returns 10.

Note: SQL Server also supports DATEDIFF(), but its behavior differs: it counts date-part boundaries crossed. PostgreSQL commonly uses date subtraction or other date-time expressions for similar calculations.

10. DATE_FORMAT() Function in SQL

The DATE_FORMAT() function in MySQL converts a date or date-time value into a string with the format you specify. It is useful when dates need to appear in a particular format in reports or query results.

Syntax:

DATE_FORMAT(date, format)

Example:

SELECT DATE_FORMAT('2026-10-27', '%d-%m-%Y') AS FormattedDate;

Output:

FormattedDate
27-10-2026

Explanation: The format string specifies the day first, followed by the month and year.

Common MySQL format specifiers include:

SpecifierMeaningExample
%dDay of the month, two digits27
%mMonth number, two digits10
%YFour-digit year2026
%MFull month nameOctober
%bAbbreviated month nameOct

Note: DATE_FORMAT() is a MySQL function. SQL Server uses functions such as FORMAT() or CONVERT() for related formatting tasks.

11. EXTRACT() Function in SQL

The EXTRACT() function retrieves a specific component, such as the year, month, or day, from a date or timestamp in supported database systems.

Syntax:

EXTRACT(part FROM date_value)

Example:

SELECT EXTRACT(YEAR FROM DATE '2026-10-27') AS OrderYear;

Output:

OrderYear
2026

Explanation: The query extracts the year from the supplied date.

The supported date parts and exact syntax can vary between database systems.

Practical Example of Date Functions in SQL

Let's consider an Orders table that stores order details.

Orders table:

OrderIDCustomerNameOrderDate
101Amit2026-10-10
102Priya2026-10-17
103Rahul2026-10-25

Suppose we want to retrieve orders placed during October 2026. We can use the following MySQL query:

SELECT OrderID, CustomerName, OrderDate FROM Orders WHERE OrderDate >= '2026-10-01'  AND OrderDate < '2026-11-01';

Output:

OrderIDCustomerNameOrderDate
101Amit2026-10-10
102Priya2026-10-17
103Rahul2026-10-25

Explanation: The query uses the SELECT statement with the WHERE clause to return orders from October 1 up to, but not including, November 1. This approach also works well when OrderDate contains a time component.

To show the newest orders first, you can add the ORDER BY clause with OrderDate DESC.

Advantages of Date Functions in SQL

Date functions help us manage date-related information without manually calculating dates or extracting individual values.

  • Retrieve current dates: Get the current date or date and time when needed.
  • Calculate date differences: Find the number of days between two dates.
  • Manage deadlines: Add or subtract days, months, or years.
  • Filter records: Retrieve orders, payments, or employee records within a specific date range.
  • Prepare reports: Extract years, months, or days and combine them with SQL aggregate functions and the HAVING clause to summarize data.
  • Format date values: Display dates in a format suitable for reports and applications.

Frequently Asked Questions (FAQs)

1. What are date functions in SQL?

Date functions are built-in functions used to retrieve, extract, compare, format, and calculate date and time values in a database.

2. Which SQL function returns the current date?

CURRENT_DATE returns the current date in supported SQL systems. In MySQL, CURDATE() is another common option.

SELECT CURDATE();

3. How do we extract the year from a date in SQL?

In MySQL and SQL Server, you can use the YEAR() function.

SELECT YEAR('2026-10-27') AS YearValue;

4. How do we calculate the difference between two dates in SQL?

In MySQL, use DATEDIFF() to calculate the difference in days.

SELECT DATEDIFF('2026-10-27', '2026-10-17') AS DaysDifference;

The result is 10.

5. Are SQL date functions the same in every database?

No. MySQL, SQL Server, PostgreSQL, and Oracle support different date functions and syntax. Always check the documentation for the database you are using.

Conclusion

SQL date functions make it easier to work with dates and times in a database. Functions such as CURRENT_DATE, YEAR(), MONTH(), DATEDIFF(), and DATE_ADD() help us retrieve date values, extract specific components, calculate differences, and manage date ranges.

Once you understand these functions, you can use them to handle common tasks involving orders, employee records, payments, reports, and other date-related information. To continue learning, explore SQL String Functions or revisit the complete SQL Tutorial.