SQL - Date & Time Functions in SQL
Date & Time functions are used to work with dates and timestamps in SQL. They help in calculations, formatting, and comparing date values. These functions are widely used in attendance systems, billing, reporting, and analytics.
Common Date & Time Functions
|
Function |
Description |
Example |
|
NOW() |
Returns current date & time |
NOW() |
|
CURDATE() |
Returns current date |
CURDATE() |
|
CURTIME() |
Returns current time |
CURTIME() |
|
DATE() |
Extracts date from datetime |
DATE(order_time) |
|
DATEDIFF(date1, date2) |
Difference between two dates |
DATEDIFF('2025-12-10','2025-12-01') |
|
DATE_ADD() |
Adds days/months/years |
DATE_ADD(date, INTERVAL 5 DAY) |
|
DATE_SUB() |
Subtracts days/months/years |
DATE_SUB(date, INTERVAL 2 MONTH) |
|
YEAR() |
Extracts year from date |
YEAR(order_date) |
|
MONTH() |
Extracts month from date |
MONTH(order_date) |
|
DAY() |
Extracts day from date |
DAY(order_date) |
Example Table
CREATE TABLE orders (
id INT,
customer_name VARCHAR(50),
order_date DATE
);
INSERT INTO orders VALUES
(1, 'Ashika', '2025-12-01'),
(2, 'Rohan', '2025-11-20');
Example Queries
1. Get today’s date:
SELECT CURDATE();
2. Find how many days ago order was placed:
SELECT customer_name, DATEDIFF(CURDATE(), order_date) AS days_ago
FROM orders;
3. Add 7 days to order date:
SELECT customer_name, DATE_ADD(order_date, INTERVAL 7 DAY) AS delivery_date
FROM orders;
Key Points
- Used for calculating ages, deadlines, and durations
- Works with DATE, TIME, and DATETIME data types
- Very important in real-time applications