Oracle 查询今日、昨日、本周、本月、本季度和本年的记录

date类型字段

//今日
select * from sv_zhjg_sheet_baseinfo where to_char(create_time,'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd') 
//昨日
select * from sv_zhjg_sheet_baseinfo where to_char(create_time,'yyyy-MM-dd')= to_char(sysdate-1,'yyyy-MM-dd') 
//本周
select * from sv_zhjg_sheet_baseinfo where to_char(create_time,'iw')=to_char(sysdate,'iw') and to_char(create_time,'yy')=to_char(sysdate,'yy')
//本月 
select * from sv_zhjg_sheet_baseinfo where to_char(create_time,'yyyy-MM')=to_char(sysdate,'yyyy-MM') 
//本季度 
select * from sv_zhjg_sheet_baseinfo where to_char(create_time,'q')=to_char(sysdate,'q') and to_char(create_time,'yy')=to_char(sysdate,'yy')
//本年
select * from sv_zhjg_sheet_baseinfo where to_char(create_time,'yyyy')=to_char(sysdate,'yyyy') 

varchar类型字段

//今日 
select * from sv_zhjg_sheet_baseinfo where to_char(to_date(create_time,'yyyy-MM-dd'),'yyyy-MM-dd')=to_char(sysdate,'yyyy-MM-dd')
//昨日
select * from sv_zhjg_sheet_baseinfo where to_char(to_date(create_time,'yyyy-MM-dd hh24:mi:ss'),'yyyy-MM-dd')=to_char(sysdate-1,'yyyy-MM-dd') 
//本周 
select * from sv_zhjg_sheet_baseinfo where to_char(to_date(create_time,'yyyy-MM-dd hh24:mi:ss'),'iw')=to_char(sysdate,'iw') and to_char(to_date(create_time,'yyyy-MM-dd hh24:mi:ss'),'yy')=to_char(sysdate,'yy')
//本月 
select * from sv_zhjg_sheet_baseinfo where to_char(to_date(create_time,'yyyy-MM-dd hh24:mi:ss'),'yyyy-MM')=to_char(sysdate,'yyyy-MM')
//本季度 
select * from sv_zhjg_sheet_baseinfo where to_char(to_date(create_time,'yyyy-MM-dd hh24:mi:ss'),'q')=to_char(sysdate,'q') and to_char(to_date(create_time,'yyyy-MM-dd hh24:mi:ss'),'yy')=to_char(sysdate,'yy')
//本年
select * from sv_zhjg_sheet_baseinfo where to_char(to_date(create_time,'yyyy-MM-dd hh24:mi:ss'),'yyyy')=to_char(sysdate,'yyyy')