MySQL Working with Dates
MySQL Working with Dates
Working with dates and time is a very important topic in MySQL, frequently asked in exams, interviews, and real projects like billing systems, attendance, logs, and reports.
MySQL
Date & Time Data Types in MySQL
| Data Type | Description | Example |
|---|---|---|
DATE | Date only | 2026-01-12 |
TIME | Time only | 14:30:00 |
DATETIME | Date + Time | 2026-01-12 14:30:00 |
TIMESTAMP | Date + Time (auto update) | 2026-01-12 14:30:00 |
YEAR | Year only | 2026 |
-
Format:
YYYY-MM-DD
Getting Current Date & Time
Current-Date
Current-Time
Current Date & Time
Extracting Parts of Date
YEAR, MONTH, DAY
DAYNAME & MONTHNAME
Using Dates in WHERE Clause
Example: Orders from 2025
Example: Records After a Date
Date Difference (Most Asked)
DATEDIFF() – Difference in Days
Output
TIMESTAMPDIFF()
Adding or Subtracting Dates
DATE_ADD()
DATE_SUB()
Formatting Dates
DATE_FORMAT()
| Format | Meaning |
|---|---|
%Y | Year |
%m | Month |
%d | Day |
%H | Hour |
%i | Minute |
%s | Second |
LAST_DAY() Function
Returns the last date of the month.
Checking Week & Day
TIMESTAMP vs DATETIME (Interview)
| Feature | TIMESTAMP | DATETIME |
|---|---|---|
| Time zone | Yes | No |
| Auto update | Yes | No |
| Storage | Less | More |
| Range | Limited | Larger |
Interview Questions & MCQs
Q1. Default format of DATE in MySQL?
A) DD-MM-YYYY
B) MM-DD-YYYY
C) YYYY-MM-DD
D) YYYY/DD/MM
Answer: C
Q2. Which function returns current date?
A) NOW()
B) CURDATE()
C) TODAY()
D) GETDATE()
Answer: B
Q3. Which function gives difference between two dates?
A) DATE_SUB()
B) DATE_ADD()
C) DATEDIFF()
D) DIFFDATE()
Answer: C
Q4. Which function formats date output?
A) FORMAT()
B) DATE_STYLE()
C) DATE_FORMAT()
D) TO_CHAR()
Answer: C
Q5. Which data type auto-updates on record change?
A) DATE
B) DATETIME
C) TIMESTAMP
D) YEAR
Answer: C
Q6. Write a query to find records of last 7 days.
Real-Life Use Cases
- Attendance systems
- Billing & invoices
- Order tracking
- Login history
- Age & experience calculation
Summary
MySQL supports powerful date & time functions
CURDATE(),NOW(),DATEDIFF()are most usedDates are essential in WHERE conditions
Very important for interviews & exams
