如何 用mysql语句获取本季度的第一天 本月的第一天,本周的第一天,谢谢,最好给出详细代码 如题 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 本周的第一天,假设周一是每周第一天。mysql> select CURDATE(),DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) DAY) ;+------------+-----------------------------------------------------+| CURDATE() | DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) DAY) |+------------+-----------------------------------------------------+| 2009-05-19 | 2009-05-18 |+------------+-----------------------------------------------------+1 row in set (0.00 sec) 本月的第一天mysql> select CURDATE(),DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY) ;+------------+---------------------------------------------------+| CURDATE() | DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY) |+------------+---------------------------------------------------+| 2009-05-19 | 2009-05-01 |+------------+---------------------------------------------------+1 row in set (0.00 sec)mysql> 本季度的第一天mysql> select CURDATE(),DATE_SUB(DATE_SUB(CURDATE(),INTERVAL (MONTH(CURDATE())-1)%3 MONTH) ,INTERVAL DAY(CURDATE())-1 DAY);+------------+----------------------------------------------------------------------------------------------------+| CURDATE() | DATE_SUB(DATE_SUB(CURDATE(),INTERVAL (MONTH(CURDATE())-1)%3 MONTH) ,INTERVAL DAY(CURDATE())-1 DAY) |+------------+----------------------------------------------------------------------------------------------------+| 2009-05-19 | 2009-04-01 |+------------+----------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> 简化一下 本季度的第一天 ,然后你可以把字符串再转换为日期。 方法很多,包括同一个函数的别名(同义词)也很多。比如CURDATE(),CURRENT_DATE(), CURRENT_DATE, NOW 等都可以返回当天mysql> select CURDATE(),ELT(QUARTER(CURDATE()), -> year(CURDATE())*1000+0101, -> year(CURDATE())*1000+0401, -> year(CURDATE())*1000+0701, -> year(CURDATE())*1000+1001) as firstDayofQ;+------------+-------------+| CURDATE() | firstDayofQ |+------------+-------------+| 2009-05-19 | 2009401 |+------------+-------------+1 row in set (0.00 sec)mysql> 用mysql语句获取本季度的第一天 本月的第一天,date(concat(year(curdate()),'-',month(curdate()),'-','1'))本周的第一天curdate()-WEEKDAY(curdate()) 用mysql语句获取本季度的第一天date(concat(year(curdate()),'-',if(quarter(curdate())=1,1,if(quarter(curdate())=2,4,if(quarter(curdate())=3,7,10)) 用mysql语句获取本季度的第一天:优化:date(concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1)) 本周的第一天date_add(date('2009-5-29'),INTERVAL -weekday('2009-5-29') day) 现有如下需求 MySQL:用IF语句判断该插入哪个值怎么写? SQL统计问题.固定行数变为不固定行数并自动取值的问题 请教关于system varialbes问题 用sql将外键 的更新和删除修改为 casade 加了order by id desc 后,语句速度很慢了? mysql-nt.exe 内存占用超大 我用MYSQL4.1.3,增加用户以后,不能设置了密码,为什么 水晶9.0总是连不上mysql??看来没有人能回答!!!8.5行 MYSQ查询问题 case when问题 求助:mysql_real_connect倒数第2个参数如何使用unix_socket?
+------------+-----------------------------------------------------+
| CURDATE() | DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) DAY) |
+------------+-----------------------------------------------------+
| 2009-05-19 | 2009-05-18 |
+------------+-----------------------------------------------------+
1 row in set (0.00 sec)
+------------+---------------------------------------------------+
| CURDATE() | DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY) |
+------------+---------------------------------------------------+
| 2009-05-19 | 2009-05-01 |
+------------+---------------------------------------------------+
1 row in set (0.00 sec)mysql>
+------------+----------------------------------------------------------------------------------------------------+
| CURDATE() | DATE_SUB(DATE_SUB(CURDATE(),INTERVAL (MONTH(CURDATE())-1)%3 MONTH) ,INTERVAL DAY(CURDATE())-1 DAY) |
+------------+----------------------------------------------------------------------------------------------------+
| 2009-05-19 | 2009-04-01 |
+------------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql>
本季度的第一天 ,然后你可以把字符串再转换为日期。 方法很多,包括同一个函数的别名(同义词)也很多。比如CURDATE(),CURRENT_DATE(), CURRENT_DATE, NOW 等都可以返回当天mysql> select CURDATE(),ELT(QUARTER(CURDATE()),
-> year(CURDATE())*1000+0101,
-> year(CURDATE())*1000+0401,
-> year(CURDATE())*1000+0701,
-> year(CURDATE())*1000+1001) as firstDayofQ;
+------------+-------------+
| CURDATE() | firstDayofQ |
+------------+-------------+
| 2009-05-19 | 2009401 |
+------------+-------------+
1 row in set (0.00 sec)mysql>
date(
concat(year(curdate()),'-',month(curdate()),'-','1'))
本周的第一天
curdate()-
WEEKDAY(curdate())
concat(year(curdate()),'-',
if(quarter(curdate())=1,1,if(quarter(curdate())=2,4,if(quarter(curdate())=3,7,10))
优化:
date(
concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1))
date_add(date('2009-5-29'),INTERVAL -weekday('2009-5-29') day)