sql日期前一天后一天查询

重生之it界大佬

于 2023-09-18 13:59:16 发布

阅读量913

点赞数

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/weixin_45650629/article/details/132975878

版权

获取前一天：
SELECT DATE_SUB('2023-01-11', INTERVAL 1 DAY) AS PreviousDay
获取后一天：
SELECT DATE_ADD(DATE_FORMAT(20230111, '%Y-%m-%d'), INTERVAL 1 DAY) AS NextDay;
日期转换格式：
SELECT DATE_FORMAT(20230111, '%Y-%m-%d') AS NextDay;

案例：
select t.hwmc2 as hwmc,t.fphm,t.fpdm,t.jshj as je,hybh,case IFNULL(type,'') when '' then '10' else type end as type,'cyfwList' as lx,case when IFNULL(sjje,'')='' then t.jshj else sjje end as sjje,
case when fplx='31' or fplx='32' then  
(SELECT count(1) FROM t_fp_qdfp qd left join t_fp fp on qd.fphm = fp.fphm 
 WHERE qd.xfsh = q.xfsh AND DATE_FORMAT(fp.fprq, '%Y-%m-%d') <= DATE_ADD(DATE_FORMAT(t.fprq, '%Y-%m-%d'), INTERVAL 1 DAY) and DATE_FORMAT(fp.fprq, '%Y-%m-%d') >= DATE_SUB(DATE_FORMAT(t.fprq, '%Y-%m-%d'), INTERVAL 1 DAY) AND qd.fphm != t.fphm)+'' 
  else (select count(1) as num from t_fp x where x.fphm<=t.fphm+25 and x.fphm>=t.fphm-25 and x.fphm!=t.fphm)+'' end as lhsl from t_fp t 
left join t_fp_qdfp q on t.fphm=q.fphm
where  deleteFlag='0' and t.hwmc='餐饮服务' and t.bxbh = 'JMSC50074160'   order by t.fphm