หาวันที่ 1 ของเดือนใน MySQL ด้วย DATE_FORMAT()

เพื่อหาวันที่ 1 ของเดือนที่ตรงกับวันที่ในคอลัมน์ ให้ใช้ DATE_FORMAT() กำหนดรูปแบบ %Y-%m-01 (วันเป็น 01 ตายตัว)

รูปแบบคำสั่ง

SELECT DATE_FORMAT(yourDateColumn, '%Y-%m-01') AS first_day_of_month 
FROM yourTableName;

ตัวอย่างการใช้งาน

1. สร้างตารางและเพิ่มวันที่กระจายเดือนต่างๆ

mysql> CREATE TABLE getFirstDayOfMonth (DueDatetime DATETIME);
Query OK, 0 rows affected (1.16 sec)

mysql> INSERT INTO getFirstDayOfMonth VALUES (DATE_ADD(NOW(), INTERVAL 3 MONTH));
mysql> INSERT INTO getFirstDayOfMonth VALUES (DATE_ADD(NOW(), INTERVAL -3 MONTH));
mysql> INSERT INTO getFirstDayOfMonth VALUES (DATE_ADD(NOW(), INTERVAL -2 MONTH));
mysql> INSERT INTO getFirstDayOfMonth VALUES (DATE_ADD(NOW(), INTERVAL -1 MONTH));
mysql> INSERT INTO getFirstDayOfMonth VALUES (DATE_ADD(NOW(), INTERVAL -4 MONTH));

2. ดูวันที่ดิบ

mysql> SELECT * FROM getFirstDayOfMonth;

+---------------------+
| DueDatetime         |
+---------------------+
| 2019-03-20 12:32:06 |
| 2018-09-20 12:32:16 |
| 2018-10-20 12:32:22 |
| 2018-11-20 12:32:28 |
| 2018-08-20 12:32:33 |
+---------------------+
5 rows in set (0.00 sec)

3. หาวันที่ 1 ของเดือนแต่ละแถว

mysql> SELECT DATE_FORMAT(DueDatetime, '%Y-%m-01') AS DateDemo 
    -> FROM getFirstDayOfMonth;

+------------+
| DateDemo   |
+------------+
| 2019-03-01 |
| 2018-09-01 |
| 2018-10-01 |
| 2018-11-01 |
| 2018-08-01 |
+------------+
5 rows in set (0.00 sec)

วิธีอื่นๆ

ใช้ DATE_SUB + DAYOFMONTH

SELECT DATE_SUB(DueDatetime, INTERVAL DAYOFMONTH(DueDatetime) - 1 DAY) 
FROM getFirstDayOfMonth;

ใช้ LAST_DAY (หาวันสุดท้ายของเดือนก่อน แล้ว +1 วัน)

SELECT DATE_ADD(LAST_DAY(DATE_SUB(DueDatetime, INTERVAL 1 MONTH)), INTERVAL 1 DAY)
FROM getFirstDayOfMonth;

ใช้ STR_TO_DATE + CONCAT (MySQL 8.0+)

SELECT STR_TO_DATE(CONCAT(YEAR(DueDatetime), '-', MONTH(DueDatetime), '-01'), '%Y-%m-%d')
FROM getFirstDayOfMonth;

กรณีใช้งานจริง

รายงานรายเดือน (Monthly Report)

SELECT 
    DATE_FORMAT(created_at, '%Y-%m-01') AS month_start,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m-01')
ORDER BY month_start DESC;

Partition Pruning (MySQL 5.7+ Partitioning)

-- Partition BY RANGE (TO_DAYS(date_col))
-- Query ที่มี WHERE date_col >= '2024-01-01' จะ Prune Partition ได้

ฟังก์ชันเกี่ยวข้อง

Timezone Note

หากคอลัมน์เป็น TIMESTAMP ค่า DATE_FORMAT จะขึ้นอยู่กับ @@session.time_zone ควรตั้ง Timezone ให้ตรงกับ Business Logic ก่อน Query