What are The Date Functions in SQL | Practical Examples?

Date functions in SQL play a crucial role in managing and manipulating date and time data. They allow you to extract, format, and perform arithmetic operations on date values effectively. In this guide, we’ll explore the most commonly used date functions in SQL with practical examples, helping you streamline your queries and improve data analysis.

---

1. Understanding Date Data Types in SQL

Before diving into date functions, it’s essential to understand the date-related data types:

• DATE: Stores date only (YYYY-MM-DD).
• DATETIME: Stores date and time (YYYY-MM-DD HH:MI:SS).
• TIMESTAMP: Similar to DATETIME but with time zone info.
• TIME: Stores only time (HH:MI:SS).

---

2. Most Common SQL Date Functions

Function	Description	Example	Output
CURRENT_DATE()	Returns current date.	SELECT CURRENT_DATE();	2025-03-08
NOW()	Returns current date and time.	SELECT NOW();	2025-03-08 14:30:00
DATEADD()	Adds a time interval to a date.	SELECT DATEADD(DAY, 5, '2025-03-08');	2025-03-13
DATEDIFF()	Returns difference between two dates.	SELECT DATEDIFF('2025-03-10', '2025-03-08');	2
DATE_FORMAT()	Formats date based on a pattern.	SELECT DATE_FORMAT(NOW(), '%Y/%m/%d');	2025/03/08
YEAR(), MONTH(), DAY()	Extracts year, month, or day from a date.	SELECT YEAR('2025-03-08');	2025

---

3. Using CURRENT_DATE() and NOW()

CURRENT_DATE()

Returns the current date without time.

sqlCopyEditSELECT CURRENT_DATE();
-- Output: 2025-03-08

NOW()

Returns both current date and time.

sqlCopyEditSELECT NOW();
-- Output: 2025-03-08 14:30:00

Practical Use Case:
Log the timestamp when a user signs up:

sqlCopyEditINSERT INTO users (username, signup_date) VALUES ('JohnDoe', NOW());

---

4. Adding and Subtracting Dates with DATEADD()

DATEADD() Syntax:

sqlCopyEditDATEADD(interval, number, date)

Example: Add 7 days to a date

sqlCopyEditSELECT DATEADD(DAY, 7, '2025-03-08');
-- Output: 2025-03-15

Example: Subtract 3 months from a date

sqlCopyEditSELECT DATEADD(MONTH, -3, '2025-03-08');
-- Output: 2024-12-08

---

5. Calculating Date Differences with DATEDIFF()

Syntax:

sqlCopyEditDATEDIFF(end_date, start_date)

Example: Calculate the number of days between two dates

sqlCopyEditSELECT DATEDIFF('2025-03-15', '2025-03-08');
-- Output: 7

Use Case: Calculate the age of a user based on birthdate:

sqlCopyEditSELECT DATEDIFF(CURRENT_DATE(), birthdate) / 365 AS age FROM users;

---

6. Formatting Dates with DATE_FORMAT()

Syntax:

sqlCopyEditDATE_FORMAT(date, format)

Example: Change date format to DD/MM/YYYY

sqlCopyEditSELECT DATE_FORMAT('2025-03-08', '%d/%m/%Y');
-- Output: 08/03/2025

Common Date Formats:

Pattern	Description	Example Output
%Y-%m-%d	ISO date format	2025-03-08
%d/%m/%Y	European date format	08/03/2025
%b %d, %Y	Abbreviated month name	Mar 08, 2025

---

7. Extracting Year, Month, and Day

You can extract specific parts of a date using these functions:

sqlCopyEditSELECT YEAR('2025-03-08') AS Year,
       MONTH('2025-03-08') AS Month,
       DAY('2025-03-08') AS Day;
-- Output: Year: 2025, Month: 3, Day: 8

---

8. Handling Time Zones with CONVERT_TZ()

When working with global applications, managing time zones is crucial.

sqlCopyEditSELECT CONVERT_TZ(NOW(), '+00:00', '+05:30') AS IST_Time;
-- Converts UTC to IST (Indian Standard Time)

---

9. Practical Examples of Date Functions

Scenario	Function Used	SQL Query Example	Expected Output
Get orders from the past 7 days	DATEDIFF() and NOW()	SELECT * FROM orders WHERE DATEDIFF(NOW(), order_date) <= 7;	Last 7 days’ orders
Calculate user’s age	YEAR() and CURRENT_DATE()	SELECT YEAR(CURRENT_DATE()) - YEAR(birthdate) AS Age FROM users;	User’s age in years
Format date for reports	DATE_FORMAT()	SELECT DATE_FORMAT(order_date, '%d-%m-%Y') FROM orders;	DD-MM-YYYY format
Get first day of current month	DATE_FORMAT() and CURRENT_DATE()	SELECT DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01');	2025-03-01

---

10. Frequently Asked Questions (FAQs)

Q1: Can I perform arithmetic operations directly on dates?

A: Yes, you can add or subtract days using the DATE_ADD() or DATE_SUB() functions.

Q2: What is the difference between DATE and DATETIME?

A: DATE stores only the date, while DATETIME includes both date and time.

---

Final Thoughts

Mastering SQL date functions is essential for effective database management. By leveraging functions like NOW(), DATEADD(), and DATE_FORMAT(), you can simplify complex queries and improve efficiency. For more SQL tips, visit our SQL Community.

For additional resources, refer to the MySQL Date and Time Functions.