🛢️ Unit 2 – SQL से Database Query · Lesson 03
SQL Date Functions
MySQL में date format
MySQL DATE values को YYYY-MM-DD और DATETIME values को YYYY-MM-DD HH:MM:SS के रूप में रखता है। Date functions इन 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 और 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 |
+-------------------------+-----------------------+-----------------------+Table पर date functions
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 | +----------+------+----------+------+
Date से filter और group
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 | +---------+-------+
DAY() values घटाना सिर्फ़ एक ही महीने के अंदर सही है। (असली दिनों के अंतर के लिए MySQL में DATEDIFF है, जो 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 | +----------+-----------+
सारांश
| 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 |
अक्सर पूछे जाने वाले प्रश्न (FAQ)
MySQL date किस format में रखता है?
YYYY-MM-DD, जैसे '2025-08-15'। DATETIME में समय भी होता है: '2025-08-15 09:05:30'। Dates quotes में लिखी जाती हैं।
NOW() और DATE(NOW()) में क्या फ़र्क है?
NOW() वर्तमान date और time देता है; DATE() सिर्फ़ date वाला हिस्सा रखता है।
MONTH() number देता है या नाम?
1 से 12 तक number। नाम (जैसे August) के लिए MONTHNAME() इस्तेमाल करें।
