Date Functions in SQL

📅 Date Functions in SQL are built-in functions used to retrieve, manipulate, format, and calculate date and time values. They help developers perform operations such as finding the current date, adding days, calculating age, extracting months, and comparing dates.

📖 What are Date Functions?

Date functions simplify working with temporal data. Instead of manually calculating dates, SQL provides functions to perform common operations quickly and accurately. Since SQL dialects differ, the names and syntax of some date functions vary between database systems.

Information

Most databases support similar date operations, but function names may differ. Always check your database documentation for the exact syntax.

đŸŽ¯ Why Use Date Functions?

Date functions make it easier to query and analyze time-based data.

  • 📌 Retrieve the current date and time.
  • 📌 Add or subtract days, months, or years.
  • 📌 Calculate the difference between dates.
  • 📌 Extract individual date parts.
  • 📌 Filter and sort records by date.

📋 Sample Table

OrderIDCustomerOrderDateDeliveryDate
101Alice2026-08-012026-08-05
102Bob2026-08-032026-08-08
103Charlie2026-08-052026-08-10

🕒 Get the Current Date and Time

Many SQL databases support standard functions for retrieving the current date and time.

Current Date and Time

SELECT
    CURRENT_DATE,
    CURRENT_TIME,
    CURRENT_TIMESTAMP;

➕ Add or Subtract Dates

Date arithmetic lets you calculate future or past dates. The exact function varies by database system.

SQL Server Example

SELECT DATEADD(day, 7, '2026-08-01') AS NextWeek;

MySQL Example

SELECT DATE_ADD('2026-08-01', INTERVAL 7 DAY) AS NextWeek;

📏 Calculate Date Difference

You can determine the number of days, months, or years between two dates using database-specific functions.

SQL Server Example

SELECT DATEDIFF(day, '2026-08-01', '2026-08-10') AS DaysBetween;

MySQL Example

SELECT DATEDIFF('2026-08-10', '2026-08-01') AS DaysBetween;

🔍 Extract Date Parts

Extracting individual parts of a date is useful for reporting and grouping.

Extract Year, Month, and Day

SELECT
    EXTRACT(YEAR FROM OrderDate) AS OrderYear,
    EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
    EXTRACT(DAY FROM OrderDate) AS OrderDay
FROM Orders;

Important

Some databases use YEAR(), MONTH(), and DAY() instead of EXTRACT().

📊 Common Date Functions

FunctionPurpose
CURRENT_DATEReturns the current date.
CURRENT_TIMEReturns the current time.
CURRENT_TIMESTAMPReturns the current date and time.
EXTRACT()Extracts parts such as year or month.
DATEADD() / DATE_ADD()Adds a time interval.
DATEDIFF()Calculates the difference between two dates.

🔎 Filter Records by Date

Orders in August 2026

SELECT *
FROM Orders
WHERE OrderDate >= '2026-08-01'
  AND OrderDate < '2026-09-01';

📈 Group Records by Month

Monthly Orders

SELECT
    EXTRACT(MONTH FROM OrderDate) AS Month,
    COUNT(*) AS TotalOrders
FROM Orders
GROUP BY EXTRACT(MONTH FROM OrderDate);

⚖ī¸ Standard vs Database-Specific Functions

OperationANSI SQLDatabase-Specific Examples
Current Date CURRENT_DATE CURDATE() (MySQL)
Current Timestamp CURRENT_TIMESTAMP GETDATE() (SQL Server)
Extract Year EXTRACT() YEAR() (MySQL, SQL Server)
Add DaysImplementation varies. DATEADD(), DATE_ADD()

đŸ’ŧ Real-World Examples

  • 🛒 Calculate estimated delivery dates.
  • 📊 Generate monthly sales reports.
  • 🎂 Calculate customer age from birth dates.
  • 📅 Find upcoming appointments.
  • đŸ“Ļ Measure shipping duration.

🗄ī¸ Database Compatibility

Database SystemDate Function Support
MySQLExtensive date functions including CURDATE(), DATE_ADD(), and DATEDIFF().
PostgreSQLStrong ANSI SQL support with EXTRACT() and interval arithmetic.
SQL ServerSupports GETDATE(), DATEADD(), DATEDIFF(), and more.
OracleRich date arithmetic and formatting functions.
SQLiteProvides built-in date/time functions such as date(), datetime(), and strftime().

⚠ī¸ Common Mistakes

  • ❌ Assuming date function names are identical across databases.
  • ❌ Using ambiguous date formats.
  • ❌ Ignoring time zone considerations.
  • ❌ Performing manual string operations instead of using date functions.

Warning

Be aware that time zones, daylight saving time, and function behavior may vary across database systems. Always test date calculations in your target DBMS.

⚠ī¸ Best Practices

Best Practice

Use built-in date functions instead of manual calculations, store dates using native date/time data types, prefer ISO 8601 date formats, use standard SQL functions where practical, and verify database-specific behavior before moving SQL code between different database systems.

🚀 Key Points to Remember

  • 📌 Date functions simplify working with temporal data.
  • 📌 Use built-in functions to retrieve, calculate, and manipulate dates.
  • 📌 Function names and syntax vary among SQL databases.
  • 📌 Standard functions improve portability when available.
  • 📌 Native date functions are more reliable than string manipulation.
  • 📌 Understanding your DBMS's date functions is essential for accurate reporting and calculations.
>>"Date functions turn time into meaningful information, making reporting, scheduling, and analysis far easier."

Summary

✅ SQL date functions provide powerful tools for retrieving, modifying, comparing, and analyzing date and time values. Whether you're calculating delivery dates, generating monthly reports, or filtering records by time, using built-in date functions leads to cleaner, more accurate, and more maintainable SQL code.