SQL Topics
Date Functions
title: Date Functions
Dates and times are among the most important types of data stored in databases. Almost every modern application records date-related information.
Examples include:
- Customer registration dates
- Employee joining dates
- Product launch dates
- Order dates
- Payment dates
- Login timestamps
- Event schedules
Simply storing dates is not enough. Businesses often need to answer questions such as:
- How many orders were placed today?
- Which customers registered this month?
- What is the difference between two dates?
- What is the current date and time?
- Which orders were placed last year?
To perform these operations efficiently, SQL provides Date Functions.
Date Functions allow developers to retrieve, manipulate, compare, format, and calculate dates and times. They are heavily used in reporting systems, dashboards, scheduling applications, financial systems, and business intelligence solutions.
What are Date Functions?
Date Functions are built-in SQL functions used to work with date and time values.
These functions help developers:
Retrieve Current Dates
Extract Date Components
Calculate Differences
Add or Subtract Time
Format Dates
Perform Date-Based FilteringExample:
SELECT CURRENT_DATE;Output:
2026-06-09The database returns today's date.
Why are Date Functions Important?
Imagine an e-commerce company.
Management asks:
How many orders were placed today?
How many customers registered this month?
Which products were sold last year?Without Date Functions:
Complex Manual CalculationsWith Date Functions:
SELECT *
FROM Orders
WHERE OrderDate = CURRENT_DATE;Results are returned instantly.
Common SQL Date Functions
Different database systems provide slightly different functions, but the most commonly used ones are:
| Function | Purpose |
|---|---|
| CURRENT_DATE | Returns current date |
| CURRENT_TIME | Returns current time |
| CURRENT_TIMESTAMP | Returns current date and time |
| NOW() | Returns current date and time |
| EXTRACT() | Extracts specific parts |
| DATE_ADD() | Adds time |
| DATE_SUB() | Subtracts time |
| DATEDIFF() | Calculates difference |
| YEAR() | Extracts year |
| MONTH() | Extracts month |
| DAY() | Extracts day |
These functions are the foundation of date manipulation in SQL.
CURRENT_DATE
The CURRENT_DATE function returns today's date.
Example:
SELECT CURRENT_DATE;Output:
2026-06-09Useful for:
- Daily reports
- Current-day transactions
- Login tracking
CURRENT_TIME
Returns the current system time.
Example:
SELECT CURRENT_TIME;Output:
14:35:20Useful for:
- Time tracking
- Scheduling
- Activity monitoring
CURRENT_TIMESTAMP
Returns both date and time.
Example:
SELECT CURRENT_TIMESTAMP;Output:
2026-06-09 14:35:20Often used for:
- Audit logs
- User activity tracking
- Transaction records
NOW() Function
Many databases support:
NOW()Example:
SELECT NOW();Output:
2026-06-09 14:35:20Provides the current date and time.
Creating a Sample Table
CREATE TABLE Orders (
OrderID INT,
CustomerName VARCHAR(100),
OrderDate DATE
);Insert sample data:
INSERT INTO Orders VALUES
(1, 'Rahul', '2026-01-15'),
(2, 'Priya', '2026-03-10'),
(3, 'Amit', '2026-06-01');YEAR() Function
Extracts the year from a date.
Example:
SELECT YEAR(OrderDate)
FROM Orders;Output:
2026
2026
2026Useful for yearly reporting.
MONTH() Function
Extracts the month value.
Example:
SELECT MONTH(OrderDate)
FROM Orders;Output:
1
3
6Useful for monthly analysis.
DAY() Function
Extracts the day value.
Example:
SELECT DAY(OrderDate)
FROM Orders;Output:
15
10
1Useful for date-based calculations.
EXTRACT() Function
EXTRACT() retrieves a specific part of a date.
Syntax:
EXTRACT(part FROM date)Example:
SELECT EXTRACT(YEAR FROM OrderDate)
FROM Orders;Output:
2026Common EXTRACT Examples
Extract Month:
SELECT EXTRACT(MONTH FROM OrderDate)
FROM Orders;Extract Day:
SELECT EXTRACT(DAY FROM OrderDate)
FROM Orders;Extract Hour:
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP);DATEDIFF() Function
Calculates the difference between two dates.
Example:
SELECT DATEDIFF(
'2026-06-09',
'2026-06-01'
);Output:
8Meaning:
8 Days DifferenceWhy DATEDIFF() is Useful
Businesses often calculate:
Employee Experience
Customer Membership Duration
Delivery Times
Project TimelinesExample:
SELECT DATEDIFF(
CURRENT_DATE,
JoinDate
)
FROM Employees;Returns the number of days since joining.
DATE_ADD() Function
Adds time to a date.
Example:
SELECT DATE_ADD(
'2026-06-01',
INTERVAL 10 DAY
);Output:
2026-06-11DATE_SUB() Function
Subtracts time from a date.
Example:
SELECT DATE_SUB(
'2026-06-10',
INTERVAL 5 DAY
);Output:
2026-06-05Filtering Records by Date
Find today's orders:
SELECT *
FROM Orders
WHERE OrderDate = CURRENT_DATE;Returns only today's orders.
Orders from a Specific Year
Example:
SELECT *
FROM Orders
WHERE YEAR(OrderDate) = 2026;Useful for annual reporting.
Orders from a Specific Month
Example:
SELECT *
FROM Orders
WHERE MONTH(OrderDate) = 6;Returns June orders.
Last 30 Days Data
Example:
SELECT *
FROM Orders
WHERE OrderDate >=
DATE_SUB(
CURRENT_DATE,
INTERVAL 30 DAY
);Useful for dashboards and analytics.
Grouping Data by Month
Example:
SELECT
MONTH(OrderDate) AS Month,
COUNT(*) AS TotalOrders
FROM Orders
GROUP BY MONTH(OrderDate);Result:
| Month | TotalOrders |
|---|---|
| 1 | 120 |
| 2 | 150 |
| 3 | 175 |
Useful for monthly reporting.
Real-World Example
Imagine an online shopping website.
Management wants:
Monthly Revenue ReportQuery:
SELECT
MONTH(OrderDate) AS Month,
SUM(TotalAmount) AS Revenue
FROM Orders
GROUP BY MONTH(OrderDate);Result:
| Month | Revenue |
|---|---|
| January | 50000 |
| February | 70000 |
| March | 90000 |
This type of report is common in business dashboards.
Date Functions and NULL Values
Consider:
| OrderDate |
|---|
| ------------ |
| 2026-01-01 |
| NULL |
| 2026-06-01 |
Functions generally ignore NULL values when calculations are performed.
Always validate data before processing.
Database Differences
Different databases may use different function names.
MySQL
NOW()
CURDATE()
DATEDIFF()PostgreSQL
CURRENT_DATE
CURRENT_TIMESTAMP
AGE()SQL Server
GETDATE()
DATEADD()
DATEDIFF()Oracle
SYSDATE
ADD_MONTHS()Understanding database-specific variations is important.
Performance Considerations
Date Functions are usually efficient.
However:
YEAR(OrderDate)on millions of rows may reduce index usage.
Better:
WHERE OrderDate
BETWEEN '2026-01-01'
AND '2026-12-31'This often performs faster.
Common Errors
Incorrect Date Format
Wrong:
'01/06/2026'Preferred:
'2026-06-01'Confusing Month and Day
Example:
2026-06-01Means:
Year-Month-DayIgnoring Time Zones
Applications used globally must handle time zones carefully.
Using Text Instead of Dates
Always store dates using proper DATE or DATETIME data types.
Best Practices
Use Standard Date Formats
Good:
YYYY-MM-DDStore Dates in Date Columns
Avoid storing dates as text.
Filter Before Processing
Reduces workload.
Use Indexes on Date Columns
Improves reporting performance.
Learn Database-Specific Functions
Every database has unique date features.
Common Interview Questions
What is CURRENT_DATE?
Returns today's date.
What does CURRENT_TIMESTAMP return?
Current date and time.
What is DATEDIFF() used for?
Calculates the difference between two dates.
What is DATE_ADD()?
Adds time to a date.
What is DATE_SUB()?
Subtracts time from a date.
Why are Date Functions important?
They simplify date calculations, reporting, scheduling, and analytics.
Summary
Date Functions are essential SQL tools for working with dates and times. They help developers perform calculations, generate reports, analyze trends, and build powerful business applications.
In this lesson, you learned:
- What Date Functions are
- CURRENT_DATE
- CURRENT_TIME
- CURRENT_TIMESTAMP
- NOW()
- YEAR()
- MONTH()
- DAY()
- EXTRACT()
- DATEDIFF()
- DATE_ADD()
- DATE_SUB()
- Date filtering
- Monthly reporting
- Database differences
- Performance considerations
- Best practices
Mastering Date Functions is important because almost every real-world application stores and analyzes date and time information.
Next Step
Continue to the next lesson:
Numeric Functions →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Date Functions.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, functions, date
Related SQL Topics