mysql中获取上个月末_mysql获取本月，本周，本季度初末数据

获取本月时间

– 本月

SELECT DATE_FORMAT( CURDATE(), ‘%Y-%m-01 00:00:00’) AS ‘本月初’;

SELECT DATE_FORMAT( LAST_DAY(CURDATE()), ‘%Y-%m-%d 23:59:59’) AS ‘本月末’;

8、查询本月的记录

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, ‘%Y%m’ ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m’ )

获取本周时间

– 本周

SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),’%w’)-1), ‘%Y-%m-%d 00:00:00’) AS ‘本周一’;

SELECT DATE_FORMAT( SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),’%w’)-7), ‘%Y-%m-%d 23:59:59’) AS ‘本周末’;

查询本季度时间

10、查询本季度数据

select * from 表名 where QUARTER(create_date)=QUARTER(now());

#当前季度的第一天：

select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-3 month),’%Y-%m-’),‘01’);

#当前季度的最后一天：

select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-1 month);

#查询本季度时间

select * from t_xcx_orders_detail t where QUARTER(t.CREATE_TIME)=QUARTER(now());

#查询本月数据

SELECT * FROM t_xcx_orders_detail t WHERE DATE_FORMAT(t.CREATE_TIME, ‘%Y%m’ ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m’ );