MySQL获取本日、本月，本周，本季度初末数据

获取本日初末数据：

SELECT DATE_FORMAT( CURDATE(), '%Y-%m-%d 00:00:00') AS '本日初';                

SELECT DATE_FORMAT( CURDATE(), '%Y-%m-%d 23:59:59') AS '本日末';                

 

获取本周初末数据：

SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1), '%Y-%m-%d 00:00:00') , '本周一'
union all
SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7), '%Y-%m-%d 23:59:59') , '本周末';

获取本月初末数据：

SELECT DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00') , '本月初'
union all
SELECT DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') , '本月末';

 

获取本季度初末数据：

select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-3 month),'%Y-%m-'),'01 00:00:00') as '本季度初';

 

select concat(DATE_FORMAT(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-1 month),'%Y-%m-%d')," 23:59:59") as '本季度末' from dual

 

补充：

-- 返回昨天
select DATE_SUB( curdate( ), INTERVAL 1 DAY )

-- 返回上周第一天
select date_sub(curdate(),interval dayofweek(curdate())-1+6 day)

-- 返回上周最后一天
select date_sub(curdate(),interval dayofweek(curdate())-1 day)

-- 返回本周第一天
select date_sub(curdate(),interval dayofweek(curdate())-2 day)

-- 返回本周最后一天
select date_add(curdate(),interval 7-dayofweek(curdate())+1 day)

-- 返回下周第一天
select date_add(curdate(),interval 7-dayofweek(curdate())+1+1 day)

-- 返回下周最后一天
select date_add(curdate(),interval 7-dayofweek(curdate())+1+7 day)

-- 返回上月第一天
select concat(year(now()),'-',lpad(month(now())-1,2,0),'-01')

-- 返回上月最后一天
select date_sub(curdate(),interval extract(DAY from now())-1+1 day)

-- 返回本月第一天
select date_sub(curdate(),interval extract(DAY from now())-1 day)

-- 返回本月最后一天
select last_day(now())

-- 返回下月第一天
select date_add(last_day(now()),interval 1 day) 

-- 返回下月最后一天
select last_day(date_add(last_day(now()),interval 1 day ))

-- 返回上年第一天
select concat(year(now())-1,'-01-01')

-- 返回上年最后一天
select concat(year(now())-1,'-12-31')

-- 返回本年第一天
select DATE_FORMAT( CURDATE(), '%Y-01-01 00:00:00') from dual;

-- 返回本年最后一天
select DATE_FORMAT( CURDATE(), '%Y-12-31 23:59:59') from dual;

-- 返回下年第一天
select date_add(concat(year(now()),'-12-31'),interval 1 day)

-- 返回下年最后一天
select concat(year(now())+1,'-12-31')

-- 返回上季度第一天
select date_sub(concat(year(now()),'-',lpad((quarter(now())-1)*3+1,2,'0'),'-01'),interval 3 month)

-- 返回上季度最后一天
select date_sub(concat(year(now()),'-',lpad((quarter(now())-1)*3+1,2,'0'),'-01'),interval 1 day)

-- 返回本季度第一天
select concat(year(now()),'-',lpad((quarter(now())-1)*3+1,2,'0'),'-01')

-- 返回本季度最后一天
select last_day(concat(year(now()),'-',lpad(quarter(now())*3,2,0),'-01'))

-- 返回下季度第一天
select concat(year(now()),'-',lpad((quarter(now()))*3+1,2,'0'),'-01')

-- 返回下季度最后一天
select last_day(concat(year(now()),'-',lpad((quarter(now())+1)*3,2,0),'-01'))