MySQL：获取当前week、month、quarter，以及date()，date_format()和str_to_date()，DATEDIFF()操作

柔情只为你懂 2023-07-08 09:28 6阅读 0赞

# 查询某年的数据 #

*  查询某年的数据

select * from oa_item_info where created like '2018-%';
    
    select * from oa_item_info where left(created,4)='2018';
    
    select * from oa_item_info where year(created)='2018';

*  今年的数据：

select * from oa_item_info where year(created)=year(now());

*  上一年的数据：

select * from oa_item_info where year(created)=year(date_sub(now(),interval 1 year));

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]

# 查询某季度的数据 #

select QUARTER(created)  as quartername ,created from oa_item_info ;

先看一下quarter函数返回的数据，第一列是quartername,第二列是created

1-3月返回1，4-6月返回2,7到9月返回3,10到12月返回4  
![在这里插入图片描述][20200226104438580.png]  
并不是搜索本季度的数据：

select * from oa_item_info where QUARTER(created)=QUARTER(now());

这条sql语句返回的是所有年份，当前季度的数据，比如现在是4月，会把所有年份4到6月的数据都检索出来

搜索本季度的数据：

加上本年的限制条件

select * from oa_item_info where QUARTER(created)=QUARTER(now()) and year(created)=year(now());

# 查询某月的数据 #

select month(created) as monthname,created from oa_item_info；

看一下返回数据：第一列是monthname,第二列是created  
![在这里插入图片描述][20200226104631678.png]  
所有年份2月的数据

select * from oa_item_info where month(created)=2；

加上年份的限定条件就可以了

查询某周的数据

select week(created) as weekname,created from oa_item_info ;

看一下返回数据：第一列是weekname,第二列是created

返回的是一年中的第几周  
![在这里插入图片描述][2020022610471495.png]  
本周的数据：

select  created   from oa_item_info where week(created)=week(now()) and year(created)=year(now());
    
    select * from oa_item_info where YEARWEEK(date_format(created,'%Y-%m-%d')) = YEARWEEK(now()) ；

看一下week和yearweek的区别：

数据库中加入两条周数一致的日期：  
![在这里插入图片描述][2020022610482850.png]  
看一下yearweek的返回值：  
![在这里插入图片描述][20200226104843269.png]  
看一下搜索结果：

select  created   from oa_item_info where week(created)=week(now());

week把两条年份不一样的都搜出来了  
![在这里插入图片描述][20200226104917661.png]

select created  from oa_item_info where YEARWEEK(date_format(created,'%Y-%m-%d')) = YEARWEEK(now()) ;
    
    select created  from oa_item_info where YEARWEEK(created) = YEARWEEK(now()) ;

不用date\_format函数也可以

yearweek只搜出了今天的  
![在这里插入图片描述][20200226104939322.png]

--------------------

未完

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/202002261043360.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg==,size_16,color_FFFFFF,t_70
[20200226104438580.png]: https://img-blog.csdnimg.cn/20200226104438580.png
[20200226104631678.png]: https://img-blog.csdnimg.cn/20200226104631678.png
[2020022610471495.png]: https://img-blog.csdnimg.cn/2020022610471495.png
[2020022610482850.png]: https://img-blog.csdnimg.cn/2020022610482850.png
[20200226104843269.png]: https://img-blog.csdnimg.cn/20200226104843269.png
[20200226104917661.png]: https://img-blog.csdnimg.cn/20200226104917661.png
[20200226104939322.png]: https://img-blog.csdnimg.cn/20200226104939322.png