🛢️ Unit 2 – Database Query using SQL · Lesson 03
SQL Date Functions
Date format in MySQL
MySQL stores DATE values as YYYY-MM-DD and DATETIME values as YYYY-MM-DD HH:MM:SS. Date functions pick out parts of these values.
📅
YEAR()2025🗓️
MONTH() / MONTHNAME()8 / August📆
DAY()15🌞
DAYNAME()Friday⏰
DATE()2025-08-15 (time removed)NOW()
SQL
SELECT NOW(), DATE(NOW()), YEAR(NOW()), MONTHNAME(NOW()), DAYNAME(NOW()); -- Output changes every second. When this page was made it showed: -- | 2026-10-19 14:57:25 | 2026-10-19 | 2026 | October | Monday |
DATE, MONTH, YEAR, DAY
SQL
SELECT DATE('2025-08-15 09:05:30'), MONTH('2025-08-15'), YEAR('2025-08-15'), DAY('2025-08-15');Output
+-----------------------------+---------------------+--------------------+-------------------+
| DATE('2025-08-15 09:05:30') | MONTH('2025-08-15') | YEAR('2025-08-15') | DAY('2025-08-15') |
+-----------------------------+---------------------+--------------------+-------------------+
| 2025-08-15 | 8 | 2025 | 15 |
+-----------------------------+---------------------+--------------------+-------------------+MONTHNAME and DAYNAME
SQL
SELECT MONTHNAME('2025-08-15'), DAYNAME('2025-08-15'), DAYNAME('2025-01-26');Output
+-------------------------+-----------------------+-----------------------+
| MONTHNAME('2025-08-15') | DAYNAME('2025-08-15') | DAYNAME('2025-01-26') |
+-------------------------+-----------------------+-----------------------+
| August | Friday | Sunday |
+-------------------------+-----------------------+-----------------------+Date functions on a table
SQL
SELECT * FROM ORDERS;
Output
+---------+----------+------------+---------------------+--------+ | OrderID | Customer | OrderDate | Delivered | Amount | +---------+----------+------------+---------------------+--------+ | 501 | Aditi | 2025-01-26 | 2025-01-29 18:40:00 | 2400 | | 502 | Rohit | 2025-03-08 | 2025-03-10 11:15:00 | 990 | | 503 | Neha | 2025-08-15 | 2025-08-20 09:05:00 | 5600 | | 504 | Karan | 2025-10-02 | 2025-10-04 16:30:00 | 1750 | | 505 | Isha | 2024-12-25 | 2024-12-27 13:00:00 | 3100 | +---------+----------+------------+---------------------+--------+
SQL
SELECT OrderID, DATE(Delivered) AS DeliveryDay FROM ORDERS;
Output
+---------+-------------+ | OrderID | DeliveryDay | +---------+-------------+ | 501 | 2025-01-29 | | 502 | 2025-03-10 | | 503 | 2025-08-20 | | 504 | 2025-10-04 | | 505 | 2024-12-27 | +---------+-------------+
SQL
SELECT Customer, DAY(OrderDate) AS D, MONTHNAME(OrderDate) AS M, YEAR(OrderDate) AS Y FROM ORDERS;
Output
+----------+------+----------+------+ | Customer | D | M | Y | +----------+------+----------+------+ | Aditi | 26 | January | 2025 | | Rohit | 8 | March | 2025 | | Neha | 15 | August | 2025 | | Karan | 2 | October | 2025 | | Isha | 25 | December | 2024 | +----------+------+----------+------+
Filtering and grouping by date
SQL
SELECT Customer, DAYNAME(OrderDate) AS OrderedOn FROM ORDERS WHERE DAYNAME(OrderDate) IN ('Saturday', 'Sunday');Output
+----------+-----------+ | Customer | OrderedOn | +----------+-----------+ | Aditi | Sunday | | Rohit | Saturday | +----------+-----------+
SQL
SELECT Customer, OrderDate FROM ORDERS WHERE YEAR(OrderDate) = 2025 AND MONTH(OrderDate) <= 6;
Output
+----------+------------+ | Customer | OrderDate | +----------+------------+ | Aditi | 2025-01-26 | | Rohit | 2025-03-08 | +----------+------------+
SQL
SELECT MONTHNAME(OrderDate) AS Month, SUM(Amount) AS Sales FROM ORDERS WHERE YEAR(OrderDate) = 2025 GROUP BY MONTH(OrderDate), MONTHNAME(OrderDate) ORDER BY MONTH(OrderDate);
Output
+---------+-------+ | Month | Sales | +---------+-------+ | January | 2400 | | March | 990 | | August | 5600 | | October | 1750 | +---------+-------+
Subtracting DAY() values works only inside the same month. (For real day differences MySQL has DATEDIFF, which is outside the syllabus.)
SQL
SELECT Customer, DAY(Delivered) - DAY(OrderDate) AS DaysTaken FROM ORDERS;
Output
+----------+-----------+ | Customer | DaysTaken | +----------+-----------+ | Aditi | 3 | | Rohit | 2 | | Neha | 5 | | Karan | 2 | | Isha | 2 | +----------+-----------+
Summary
| Function | Returns |
|---|---|
| NOW() | Current date and time |
| DATE(d) | Date part of a date-time |
| MONTH(d) | Month number 1–12 |
| MONTHNAME(d) | Month name |
| YEAR(d) | Year |
| DAY(d) | Day of the month 1–31 |
| DAYNAME(d) | Weekday name |
Frequently Asked Questions
In which format does MySQL store a date?
YYYY-MM-DD, for example '2025-08-15'. DATETIME adds the time: '2025-08-15 09:05:30'. Dates are written in quotes.
What is the difference between NOW() and DATE(NOW())?
NOW() returns the current date and time; DATE() keeps only the date part.
What does MONTH() return – a number or a name?
A number from 1 to 12. Use MONTHNAME() for the name, such as August.
