MySQL中如何Select出本周第一天的日期？

Question

我知道当日时可以用
select curdate();来实现，
那么我想select 出本周周一的日期呢？
还有本月第一天的日期呢？？

Answer 1

本周周一
mysql> select curdate(), date_sub(curdate(),interval WEEKDAY(curdate()) day);
+------------+-----------------------------------------------------+
| curdate()  | date_sub(curdate(),interval WEEKDAY(curdate()) day) |
+------------+-----------------------------------------------------+
| 2009-09-03 | 2009-08-31                                          |
+------------+-----------------------------------------------------+
1 row in set (0.00 sec)mysql>

Answer 2

本月第一天的日期mysql> select curdate(), date_sub(curdate(),interval DAY(curdate())-1 day);
+------------+---------------------------------------------------+
| curdate()  | date_sub(curdate(),interval DAY(curdate())-1 day) |
+------------+---------------------------------------------------+
| 2009-09-03 | 2009-09-01                                        |
+------------+---------------------------------------------------+
1 row in set (0.00 sec)mysql>

Answer 3

mysql> select date_format(concat(year(curdate()),'-',month(curdate()),'-','1'),"
%Y/%m/%d");
+------------------------------------------------------------------------------+| date_format(concat(year(curdate()),'-',month(curdate()),'-','1'),"%Y/%m/%d") |+------------------------------------------------------------------------------+| 2009/09/01                                                                   |+------------------------------------------------------------------------------+1 row in set (0.00 sec)

Answer 4

mysql>  select curdate(), date_add(curdate(),interval -WEEKDAY(curdate()) day);
+------------+------------------------------------------------------+
| curdate()  | date_add(curdate(),interval -WEEKDAY(curdate()) day) |
+------------+------------------------------------------------------+
| 2009-09-03 | 2009-08-31                                           |
+------------+------------------------------------------------------+
1 row in set (0.00 sec)

Answer 5

--本周周一mysql> select subdate(curdate(),date_format(curdate(),'%w')-1);
+--------------------------------------------------+
| subdate(curdate(),date_format(curdate(),'%w')-1) |
+--------------------------------------------------+
| 2009-09-14                                       |
+--------------------------------------------------+
1 row in set (0.00 sec) 
--本月第一天mysql> select concat(left(curdate(),8),'01');
+--------------------------------+
| concat(left(curdate(),8),'01') |
+--------------------------------+
| 2009-09-01                     |
+--------------------------------+
1 row in set (0.00 sec)

Answer 6

mysql> select subdate(curdate(),date_format(curdate(),'%w')-1);
+--------------------------------------------------+
| subdate(curdate(),date_format(curdate(),'%w')-1) |
+--------------------------------------------------+
| 2009-08-31                                       |
+--------------------------------------------------+
1 row in set (0.00 sec)
汗，刚才调了日期来测试忘了调过来

Answer 7

date_add(CURDATE(),INTERVAL -weekday(CURDATE()) day)

Answer 8

date_add(CURDATE(),INTERVAL -weekday(CURDATE()) day)

Answer 9

date_add(CURDATE(),INTERVAL -weekday(CURDATE()) day)

Answer 10

mysql> select now() ,date_sub(now(),interval dayofweek(now())-2 day);
+---------------------+-------------------------------------------------+
| now()               | date_sub(now(),interval dayofweek(now())-2 day) |
+---------------------+-------------------------------------------------+
| 2009-09-04 09:50:23 | 2009-08-31 09:50:23                             |
+---------------------+-------------------------------------------------+
1 row in set (0.00 sec)mysql>