Date and Time Functions in SQL with Examples

Working with dates and times is a core part of data analysis. Numerous built-in functions in SQL facilitate the extraction, manipulation, and computation of date and time variables. Let’s go through the most useful Date and Time Functions one by one with clear examples.

---

Introduction

1. NOW() or CURRENT_TIMESTAMP()

• Description: Returns the current date and time.
• Example:

SELECT NOW();

-- Output: 2025-08-17 14:25:36

This gives both the date and the time at the moment of query execution.

---

2. CURDATE() or CURRENT_DATE()

• Description: Returns the current date only.
• Example:

SELECT CURDATE();

-- Output: 2025-08-17

---

3. CURTIME()

• Description: Returns the current time only.
• Example:

SELECT CURTIME();

-- Output: 14:25:36

---

4. DATE()

• Description: Takes a DATETIME value and extracts the date portion.
• Example:

SELECT DATE('2025-08-17 14:25:36');

-- Output: 2025-08-17

---

5. TIME()

• Description: Takes a DATETIME value and extracts the time component.
• Example:

SELECT TIME('2025-08-17 14:25:36');

-- Output: 14:25:36

---

6. YEAR()

• Description: Extracts the year from a date.
• Example:

SELECT YEAR('2025-08-17');

-- Output: 2025

---

7. MONTH()

• Description: Takes a date (1–12) and extracts the month from it.
• Example:

SELECT MONTH('2025-08-17');

-- Output: 8

---

8. DAY()

• Description: Extracts the day of the month from a date (1–31).
• Example:

SELECT DAY('2025-08-17');

-- Output: 17

---

9. DATE_ADD()

• Description: Enhances a date with a specified time interval.
• Example:

SELECT DATE_ADD('2025-08-17', INTERVAL 10 DAY);

-- Output: 2025-08-27

---

10. DATE_SUB()

• Description: Deducts a given amount of time from a date.
• Example:

SELECT DATE_SUB('2025-08-17', INTERVAL 5 DAY);

-- Output: 2025-08-12

---

11. TIMESTAMPDIFF()

• Description: Returns the difference between two timestamps in a specified unit (like days, months, or years).
• Example:

SELECT TIMESTAMPDIFF(DAY, '2025-08-01', '2025-08-17');

-- Output: 16

---

12. EXTRACT()

• Description: Takes a specific portion of a date (year, month, day, etc.).
• Example:

SELECT EXTRACT(YEAR FROM '2025-08-17');

-- Output: 2025

SELECT EXTRACT(MONTH FROM '2025-08-17');

-- Output: 8

---

📌 Why These Functions Matter

• Data Cleaning: Easily extract parts of dates (YEAR, MONTH, DAY).
• Business Analysis: Calculate deadlines (DATE_ADD, DATE_SUB).
• Reporting: Find trends across time periods.
• Time Calculations: Measure durations (TIMESTAMPDIFF).

Mastering these SQL Date and Time functions will help you work faster and smarter with time-based data.