Sql Server – MySQL 时间查询

SQL Server

查询昨天的数据

select * from table where datediff(day, 时间字段,getdate()) = 1
1
查询今天的数据

SELECT * FROM checkinfo where DATEDIFF(day,时间字段,GETDATE())=0;
1
查询昨天的数据

SELECT * FROM checkinfo where DATEDIFF(day,时间字段,GETDATE())=1;
1
查询本周的数据

SELECT * FROM checkinfo where datediff(week,c_sample_date,getdate())=0;
1
查询上周的数据

select * from checkinfo where datediff(week, 时间字段 ,getdate()) = 1;
1
查询下周的数据

select * from checkinfo where datediff(week, 时间字段 ,getdate()) = -1;
1
查询上月的数据

select * From checkinfo Where DateDiff(month, 时间字段, GetDate()) = 1;
1
查询本月的数据

select * From checkinfo Where DateDiff(month, 时间字段, GetDate()) = 0;
1
查询下月的数据

Select * From checkinfo Where DateDiff(month,时间字段,GetDate()) = -1;
1
查询最近七天的数据

Select * From checkinfo Where DateDiff(dd, 时间字段, GetDate()) <= 7
1
查询今年的数据

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

MySQL

查询今天的数据

select * from table where to_days(时间字段) = to_days(now());
1
查询本周的数据

SELECT * FROM  table WHERE YEARWEEK( date_format(时间字段名,'%Y-%m-%d' ) ) = YEARWEEK( now() ) ;
1
查询本月的数据

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');
1
2
3
–查询本年数据

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