sql 获取某年 某月的数据

–查询某年的数据
select * from table where YEAR(createTime) = 2018;

–查询某月的数据 将数据库中的时间字段格式改为字符串格式。
select * from combine where CONVERT(varchar(7),create_time, 120 ) = ‘2018-05’
或者
select * from combine where YEAR(create_time)=2018 and month(create_time)=5;

sqlserver

查询昨天的数据

select * from table where datediff(day, 时间字段,getdate()) = 1

查询今天的数据

SELECT * FROM checkinfo where DATEDIFF(day,时间字段,GETDATE())=0;

查询昨天的数据

SELECT * FROM checkinfo where DATEDIFF(day,时间字段,GETDATE())=1;

查询本周的数据

SELECT * FROM checkinfo where datediff(week,c_sample_date,getdate())=0;

查询上周的数据

select * from checkinfo where datediff(week, 时间字段 ,getdate()) = 1;

查询下周的数据

select * from checkinfo where datediff(week, 时间字段 ,getdate()) = -1;

查询上月的数据

select * From checkinfo Where DateDiff(month, 时间字段, GetDate()) = 1;

查询本月的数据

select * From checkinfo Where DateDiff(month, 时间字段, GetDate()) = 0;

查询下月的数据

Select * From checkinfo Where DateDiff(month,时间字段,GetDate()) = -1;

查询最近七天的数据

Select * From checkinfo Where DateDiff(dd, 时间字段, GetDate()) <= 7

查询今年的数据

Select * From checkinfo Where DateDiff(year, GetDate(), c_sample_date ) = 0

mysql

查询今天的数据

select * from table where to_days(时间字段) = to_days(now());

查询本周的数据

SELECT * FROM  table WHERE YEARWEEK( date_format(时间字段名,'%Y-%m-%d' ) ) = YEARWEEK( now() ) ;

查询本月的数据

SELECT * FROM table WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' );
或
select * from table where date_format( 时间字段名,'%Y-%m')=date_format(now(),'%Y-%m');

–查询本年数据

select * from table  where YEAR(时间字段名)=YEAR(NOW());