Date & Time Functions0%

Control Flow Functions (IF, IFNULL, COALESCE)

Date & Time Functions

Beginner12 min read•Updated: 2026-09-12

Date & Time Functions: Current Timestamps, Arithmetic, and Formatting

Handling temporal data is essential for computing delivery estimates, subscription expirations, user ages, and financial reporting periods. MySQL offers an extensive library of Date and Time Functions that allow you to retrieve system clocks, execute calendar arithmetic, and format dates for display.

1. Getting the Current Date & Time

Function	Returns	Format
`NOW()` / `CURRENT_TIMESTAMP`	Current date and time	`'YYYY-MM-DD HH:MM:SS'`
`CURDATE()` / `CURRENT_DATE`	Current date only	`'YYYY-MM-DD'`
`CURTIME()` / `CURRENT_TIME`	Current time only	`'HH:MM:SS'`

1SELECT 
  NOW() AS current_moment,
  CURDATE() AS today,
  CURTIME() AS right_now;

2. Extracting Parts of a Date

1SELECT 
  order_date,
  YEAR(order_date) AS order_year,        -- e.g. 2026
  MONTH(order_date) AS order_month_num,  -- e.g. 3
  MONTHNAME(order_date) AS month_name,   -- e.g. 'March'
  DAY(order_date) AS day_of_month,       -- e.g. 15
  DAYNAME(order_date) AS day_name,       -- e.g. 'Sunday'
  QUARTER(order_date) AS fiscal_quarter  -- e.g. 1
9FROM orders;

3. Date Arithmetic: `DATE_ADD()` and `DATE_SUB()`

Never perform date arithmetic using simple addition (order_date + 7). Use dedicated date interval functions to account for varying month lengths and leap years:

1-- Calculate subscription renewal date (Add 1 Month):
2SELECT DATE_ADD(CURDATE(), INTERVAL 1 MONTH) AS renewal_date;
3 
4-- Calculate 30-day warranty expiration:
5SELECT DATE_ADD(purchase_date, INTERVAL 30 DAY) AS warranty_end FROM sales;
6 
7-- Calculate 90 days ago:
8SELECT DATE_SUB(CURDATE(), INTERVAL 90 DAY) AS ninety_days_ago;

4. Calculating Time Elapsed: `DATEDIFF()`

The DATEDIFF(date1, date2) function returns the number of days between two dates (date1 - date2):

1-- How many days did order fulfillment take?
2SELECT 
3    order_id, 
4    order_date, 
5    delivery_date,
6    DATEDIFF(delivery_date, order_date) AS fulfillment_days
7FROM shipments;
8 
9-- Calculate user age in days:
10SELECT DATEDIFF(CURDATE(), '2000-01-01') AS days_alive;

Note

[!NOTE] For calculating exact differences in hours, minutes, or seconds, use TIMESTAMPDIFF(unit, datetime1, datetime2): SELECT TIMESTAMPDIFF(HOUR, login_time, logout_time) AS hours_online FROM sessions;

5. Custom Date Formatting with `DATE_FORMAT()`

When presenting dates on invoices or reports, use DATE_FORMAT(date, format_string):

Specifier	Description	Example
`%Y`	4-digit Year	`2026`
`%y`	2-digit Year	`26`
`%M`	Full Month Name	`March`
`%m`	2-digit Month Number	`03`
`%d`	2-digit Day of Month	`15`
`%W`	Full Weekday Name	`Sunday`
`%h` / `%i` / `%p`	12-hour / Minutes / AM-PM	`02:30 PM`

1SELECT 
2    order_date,
3    DATE_FORMAT(order_date, '%W, %d %M %Y') AS readable_date,
4    DATE_FORMAT(NOW(), '%d/%m/%Y %h:%i %p') AS indian_standard_format
5FROM orders;
6-- Output: 'Sunday, 15 March 2026' and '15/03/2026 02:30 PM'

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which function returns both the current system date and time in MySQL?

Question 2

How do you calculate a date exactly 3 weeks in the future from today in MySQL?

Question 3

What does DATEDIFF('2026-03-20', '2026-03-15') return?

Question 4

Which format string in DATE_FORMAT() outputs the full 4-digit year (e.g., '2026')?

Question 5

How can you calculate an employee's exact age in years from their birth_date?

Next Lesson

Control Flow Functions (IF, IFNULL, COALESCE)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Numeric & Math Functions	Control Flow Functions (IF, IFNULL, COALESCE)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Control Flow Functions (IF, IFNULL, COALESCE)

Date & Time Functions

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Date & Time Functions: Current Timestamps, Arithmetic, and Formatting

1. Getting the Current Date & Time

Function	Returns	Format
`NOW()` / `CURRENT_TIMESTAMP`	Current date and time	`'YYYY-MM-DD HH:MM:SS'`
`CURDATE()` / `CURRENT_DATE`	Current date only	`'YYYY-MM-DD'`
`CURTIME()` / `CURRENT_TIME`	Current time only	`'HH:MM:SS'`

1SELECT 
  NOW() AS current_moment,
  CURDATE() AS today,
  CURTIME() AS right_now;

2. Extracting Parts of a Date

1SELECT 
  order_date,
  YEAR(order_date) AS order_year,        -- e.g. 2026
  MONTH(order_date) AS order_month_num,  -- e.g. 3
  MONTHNAME(order_date) AS month_name,   -- e.g. 'March'
  DAY(order_date) AS day_of_month,       -- e.g. 15
  DAYNAME(order_date) AS day_name,       -- e.g. 'Sunday'
  QUARTER(order_date) AS fiscal_quarter  -- e.g. 1
9FROM orders;

3. Date Arithmetic: `DATE_ADD()` and `DATE_SUB()`

Never perform date arithmetic using simple addition (order_date + 7). Use dedicated date interval functions to account for varying month lengths and leap years:

1-- Calculate subscription renewal date (Add 1 Month):
2SELECT DATE_ADD(CURDATE(), INTERVAL 1 MONTH) AS renewal_date;
3 
4-- Calculate 30-day warranty expiration:
5SELECT DATE_ADD(purchase_date, INTERVAL 30 DAY) AS warranty_end FROM sales;
6 
7-- Calculate 90 days ago:
8SELECT DATE_SUB(CURDATE(), INTERVAL 90 DAY) AS ninety_days_ago;

4. Calculating Time Elapsed: `DATEDIFF()`

The DATEDIFF(date1, date2) function returns the number of days between two dates (date1 - date2):

1-- How many days did order fulfillment take?
2SELECT 
3    order_id, 
4    order_date, 
5    delivery_date,
6    DATEDIFF(delivery_date, order_date) AS fulfillment_days
7FROM shipments;
8 
9-- Calculate user age in days:
10SELECT DATEDIFF(CURDATE(), '2000-01-01') AS days_alive;

Note

5. Custom Date Formatting with `DATE_FORMAT()`

When presenting dates on invoices or reports, use DATE_FORMAT(date, format_string):

Specifier	Description	Example
`%Y`	4-digit Year	`2026`
`%y`	2-digit Year	`26`
`%M`	Full Month Name	`March`
`%m`	2-digit Month Number	`03`
`%d`	2-digit Day of Month	`15`
`%W`	Full Weekday Name	`Sunday`
`%h` / `%i` / `%p`	12-hour / Minutes / AM-PM	`02:30 PM`

1SELECT 
2    order_date,
3    DATE_FORMAT(order_date, '%W, %d %M %Y') AS readable_date,
4    DATE_FORMAT(NOW(), '%d/%m/%Y %h:%i %p') AS indian_standard_format
5FROM orders;
6-- Output: 'Sunday, 15 March 2026' and '15/03/2026 02:30 PM'

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which function returns both the current system date and time in MySQL?

Question 2

How do you calculate a date exactly 3 weeks in the future from today in MySQL?

Question 3

What does DATEDIFF('2026-03-20', '2026-03-15') return?

Question 4

Which format string in DATE_FORMAT() outputs the full 4-digit year (e.g., '2026')?

Question 5

How can you calculate an employee's exact age in years from their birth_date?

Next Lesson

Control Flow Functions (IF, IFNULL, COALESCE)

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Numeric & Math Functions	Control Flow Functions (IF, IFNULL, COALESCE)

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Date & Time Functions: Current Timestamps, Arithmetic, and Formatting

1. Getting the Current Date & Time

2. Extracting Parts of a Date

3. Date Arithmetic: DATE_ADD() and DATE_SUB()

4. Calculating Time Elapsed: DATEDIFF()

5. Custom Date Formatting with DATE_FORMAT()

Multiple Choice Questions

Control Flow Functions (IF, IFNULL, COALESCE)

Related Lessons

Practice Quiz

Loading Page Content...

Date & Time Functions: Current Timestamps, Arithmetic, and Formatting

1. Getting the Current Date & Time

2. Extracting Parts of a Date

3. Date Arithmetic: DATE_ADD() and DATE_SUB()

4. Calculating Time Elapsed: DATEDIFF()

5. Custom Date Formatting with DATE_FORMAT()

Multiple Choice Questions

Control Flow Functions (IF, IFNULL, COALESCE)

Related Lessons

Practice Quiz

3. Date Arithmetic: `DATE_ADD()` and `DATE_SUB()`

4. Calculating Time Elapsed: `DATEDIFF()`

5. Custom Date Formatting with `DATE_FORMAT()`

3. Date Arithmetic: `DATE_ADD()` and `DATE_SUB()`

4. Calculating Time Elapsed: `DATEDIFF()`

5. Custom Date Formatting with `DATE_FORMAT()`