You can use DATE_FORMAT to recreate the first of the month:
mysql> select d, DATE_FORMAT(d, '%Y-%m-01') FROM date_val; +------------+----------------------------+ | d | DATE_FORMAT(d, '%Y-%m-01') | +------------+----------------------------+ | 1864-02-28 | 1864-02-01 | | 1900-01-15 | 1900-01-01 | | 1987-03-05 | 1987-03-01 | | 1999-12-31 | 1999-12-01 | | 2000-06-04 | 2000-06-01 | | 2004-01-01 | 2004-01-01 | +------------+----------------------------+
DATE_SUB and DATE_FORMAT will generate the first day of the week, assuming Sunday starts your week:
mysql> select d, DATE_SUB(d, INTERVAL DATE_FORMAT(d, '%w') DAY) FROM date_val;
+------------+------------------------------------------------+
| d | DATE_SUB(d, INTERVAL DATE_FORMAT(d, '%w') DAY) |
+------------+------------------------------------------------+
| 1864-02-28 | 1864-02-28 |
| 1900-01-15 | 1900-01-14 |
| 1987-03-05 | 1987-03-01 |
| 1999-12-31 | 1999-12-26 |
| 2000-06-04 | 2000-06-04 |
| 2004-01-01 | 2003-12-28 |
+------------+------------------------------------------------+
If your week starts on Monday, you can simply use WEEKDAY:
mysql> select d, DATE_SUB(d, INTERVAL WEEKDAY(d) DAY) FROM date_val; +------------+--------------------------------------+ | d | DATE_SUB(d, INTERVAL WEEKDAY(d) DAY) | +------------+--------------------------------------+ | 1864-02-28 | 1864-02-22 | | 1900-01-15 | 1900-01-15 | | 1987-03-05 | 1987-03-02 | | 1999-12-31 | 1999-12-27 | | 2000-06-04 | 2000-05-29 | | 2004-01-01 | 2003-12-29 | +------------+--------------------------------------+
As a side note, Paul DuBois lists several useful date calculations such as last day of the month on pages 265-267 of the MySQL Cookbook (O'Reilly). He uses a DATE_SUB routine for generating the first of the month, so maybe his way is faster.
____________________________________________________________ Eamon Daly
----- Original Message ----- From: "Chris Knipe" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, October 18, 2004 9:07 PM
Subject: first day of week/month
Hi,
I know this might be a little silly, but can anyone give me a example on how to get the date of the first day of a week and month?
-- Chris.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
