• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Dates

← Previous Next →

Working with dates is one of the most common tasks in database management. Whether it’s tracking an order, storing a student’s birth date, or recording when a record was created, MySQL provides a complete set of tools for handling and manipulating date and time values. In this tutorial, we’ll learn everything about MySQL date types, functions, formatting, and calculations.

What Are MySQL Date and Time Data Types?

MySQL provides several data types to store different kinds of date and time values. Each serves a specific purpose depending on what information you want to record.

Data Type Description Example
DATE Stores only the date (year, month, day). 2025-10-23
TIME Stores only the time (hour, minute, second). 14:35:20
DATETIME Stores both date and time. 2025-10-23 14:35:20
TIMESTAMP Similar to DATETIME, but stored in UTC and converts automatically based on time zone. 2025-10-23 09:05:30
YEAR Stores only a year value (2-digit or 4-digit). 2025

  • Use DATE when you only care about the date (like birthdays).

  • Use DATETIME or TIMESTAMP when time is also important (like order creation time).

How to Insert Dates in MySQL

MySQL expects dates in a specific format:

  • Date only: YYYY-MM-DD

  • Date and time: YYYY-MM-DD HH:MM:SS

Example:

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_date DATE,
  delivery_time DATETIME
);

INSERT INTO orders (order_date, delivery_time)
VALUES ('2025-10-23', '2025-10-23 15:30:00');

⚠️ Tip: If you enter an invalid format, MySQL will store 0000-00-00, which represents an invalid date. Always double-check your formats before inserting data.

How to Retrieve and Format Dates

Dates in MySQL are stored in a standard format, but you can display them however you like using the DATE_FORMAT() function.

SELECT DATE_FORMAT(order_date, '%M %d, %Y') AS formatted_date
FROM orders;

Output:

October 23, 2025

Common format specifiers:

  • %Y → Year (4 digits)

  • %m → Month number (01–12)

  • %M → Full month name (January–December)

  • %d → Day of the month (01–31)

  • %H:%i:%s → Hour:Minute:Second (24-hour format)

How to Extract Parts of a Date

MySQL lets you extract specific parts (like year or month) from a date using simple functions.

Function Description Example Result
YEAR() Returns the year YEAR('2025-10-23') 2025
MONTH() Returns the month number MONTH('2025-10-23') 10
DAY() Returns the day of the month DAY('2025-10-23') 23
HOUR() Returns the hour HOUR('15:30:00') 15
MINUTE() Returns the minute MINUTE('15:30:00') 30

Example:

SELECT * FROM orders WHERE MONTH(order_date) = 10;

This query retrieves all orders made in October.

How to Perform Date Calculations

You can add or subtract time intervals from a date using DATE_ADD() and DATE_SUB().

SELECT 
  order_date,
  DATE_ADD(order_date, INTERVAL 5 DAY) AS expected_delivery
FROM orders;

Output:

2025-10-23 | 2025-10-28

Similarly:

SELECT DATE_SUB(order_date, INTERVAL 3 DAY) AS three_days_before
FROM orders;

To find the difference between two dates (in days), use DATEDIFF():

SELECT DATEDIFF('2025-10-30', '2025-10-23') AS days_between;

Result: 7

How to Get the Current Date and Time

MySQL has built-in functions that return the current system date and time.

Function Description Example Result
NOW() Returns the current date and time 2025-10-23 14:45:12
CURDATE() Returns the current date only 2025-10-23
CURTIME() Returns the current time only 14:45:12

Example:

INSERT INTO orders (order_date, delivery_time)
VALUES (CURDATE(), NOW());

This automatically records the current date and time when an order is added.

How to Compare Dates in MySQL

You can compare date values directly, just like numbers.

Example 1:

SELECT * FROM orders
WHERE order_date > '2025-10-01';

→ Retrieves all orders placed after October 1, 2025.

Example 2:

SELECT * FROM orders
WHERE order_date BETWEEN '2025-10-01' AND '2025-10-31';

→ Retrieves all orders placed in October 2025.

How to Handle Time Zones with TIMESTAMP

DATETIME stores date and time as entered.
TIMESTAMP stores time in UTC and converts it based on the server or user’s time zone.

This makes TIMESTAMP ideal for applications used across multiple regions.

Example:

CREATE TABLE logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  activity_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Whenever you insert a new record, MySQL stores the UTC time automatically and adjusts it when retrieved based on your current time zone.

Common Date Errors and How to Avoid Them

Here are some common mistakes when working with dates:

  • ❌ Invalid format: Always use YYYY-MM-DD or YYYY-MM-DD HH:MM:SS.

  • ❌ Zero date (0000-00-00): Happens when you insert an invalid date.

  • ❌ Wrong sorting: Never store dates as text; always use date data types.

  • ❌ Time zone mismatch: Use TIMESTAMP for time-sensitive apps.

Practical Example

Let’s go through a real-world example with events.

CREATE TABLE events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_name VARCHAR(100),
  event_date DATETIME
);

INSERT INTO events (event_name, event_date)
VALUES
('Conference', '2025-10-23 10:00:00'),
('Webinar', '2025-11-05 17:00:00'),
('Workshop', '2025-12-01 09:30:00');

Find events in November:

SELECT event_name FROM events
WHERE MONTH(event_date) = 11;

Find upcoming events in the next 30 days:

SELECT event_name FROM events
WHERE event_date BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY);

These queries are useful for scheduling systems, booking apps, and dashboards that rely on date-based data.

Summary of the Tutorial

Dates in MySQL are powerful tools for managing time-related information.
You learned about:

  • Different date and time data types

  • Formatting and extracting date parts

  • Performing date arithmetic

  • Comparing and filtering by dates

  • Handling time zones using TIMESTAMP

When used correctly, MySQL’s date functions make data analysis and reporting much easier, whether it’s tracking sales, scheduling events, or analyzing trends.

Practice Questions

  1. Create a table named students with columns id, name, and birth_date. Insert 3 records with different birth dates.

  2. Write a query to display all students whose birth date is after '2005-01-01'.

  3. Use the YEAR() function to extract and display only the year from each student’s birth_date.

  4. Write a query to show students born in the month of May using the MONTH() function.

  5. Insert a record into a table named meetings with the current date and time using CURDATE() and NOW().

  6. Create a query that adds 7 days to the order_date column in an orders table and displays it as delivery_date.

  7. Write a query using DATEDIFF() to find the number of days between today’s date and each student’s birth_date.

  8. Display all events from an events table that are scheduled between '2025-10-01' and '2025-12-31'.

  9. Create a table named activity_log with a login_time column that automatically stores the current timestamp whenever a new record is inserted.

  10. Format the order_date from an orders table to display it as October 23, 2025 using DATE_FORMAT().

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top