mysql 获取时间日期的方法、函数
-获取当前的年份
date_format( curdate(),’%Y’)
- 获取当年的月份
date_format( curdate(),’%m’)
- 获取当前的 年 月 日
date_format( curdate(),’%Y%m%d’)
- 获取某一个月的第几天
DAYOFMONTH(date_format( curdate(),’%Y%m%d’))
- 查询当月的数据
SELECT * FROM `vtiger_order` WHEREdate_format( 时间字段,'%Y-%m') = date_format(now(),'%Y-%m');
- 查询上个月的数据
select name,submittime from enterprise where date_format(submittime,’%Y-%m’)=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),’%Y-%m’)
- 获取本年的数据
SELECT
financeoperatetime
FROM
`vtiger_order`
WHERE
date_format(financeoperatetime, '%Y') = date_format(now(), '%Y');
-获取上一年的数据
SELECT
financeoperatetime
FROM
vtiger_order
WHERE
date_format(financeoperatetime, '%Y') = date_format(DATE_SUB(curdate(), INTERVAL 1 YEAR),'%Y')
获取上一年同天的数据
SELECT
financeoperatetime
FROM
vtiger_order
WHERE
date_format(financeoperatetime, '%Y%m%d') = date_format(DATE_SUB(curdate(), INTERVAL 1 YEAR),'%Y%m%d')
- 获取上一年同月的数据
SELECT
financeoperatetime
FROM
vtiger_order
WHERE
date_format(financeoperatetime, '%Y%m') = date_format(DATE_SUB(curdate(), INTERVAL 1 YEAR),'%Y%m')
- 查询距离当前现在6个月的数据
select
name,submittime
from
enterprise
where
submittime between date_sub(now(),interval 6 month) and now();
- 获取上一周的数据
SELECT
financeoperatetime
FROM
vtiger_order
WHERE
YEARWEEK(date_format(financeoperatetime, '%Y%m%d')) = YEARWEEK(now()) - 1;
- 查询当前这周的数据
SELECT
financeoperatetime
FROM
vtiger_order