史上最全mysql日期计算（月初、月末、上中下旬判断、年初、年末、季初、季末）
其中 startDay 格式为 = ‘2021-01-01’，替换即可使用，如果对你有帮助的话，请多多关注，感谢大家~~select DATE_FORMAT(startDay,'%Y%m%d')#3.日期(格式YYYYMMDD)select DATE_FORMAT(startDay,'%Y-%m-%d')#4.日期(格式YYYY-MM-DD)select DATE_FORMAT(startDay,
·
其中 startDay 格式为 = ‘2021-01-01’，替换即可使用，如果对你有帮助的话，请多多关注，感谢大家~~
select DATE_FORMAT(startDay,'%Y%m%d') #3.日期(格式YYYYMMDD)
select DATE_FORMAT(startDay,'%Y-%m-%d') #4.日期(格式YYYY-MM-DD)
select DATE_FORMAT(startDay,'%Y/%m/%d') #5.日期(格式YYYY/MM/DD)
SET workOrWeek = DAYOFWEEK(DATE_SUB(startDay,INTERVAL 1 day));
IF (workOrWeek)>5 THEN
SET db_status=0;
ELSE
SET db_status=1;
END IF; #6.工作日标志
set WkBgnFlg=(select case when DAYOFWEEK(startDay) ='2' then 1 else 0 end ); #7.周初标志
set BgnTenDyFlg=(select case DAYOFMONTH(startDay) when '1' then 1 when '11' then 1 when '21' then 1 else 0 end ); #8.旬初标志
set MoBgnFlg=(select case DAYOFMONTH(startDay) when '1' then 1 else 0 end ); #9.月初标志
set QuaBgnFlg=(select case date(startDay) when concat(year(startDay),'-01-01') then 1
when concat(year(startDay),'0401') then 1
when concat(year(startDay),'0701') then 1
when concat(year(startDay),'1001') then 1
else 0 end ); #10.季初标志
set YrBgnFlg=(SELECT case DAYOFYEAR(startDay) when '1' then 1 else 0 end ); #11.年初标志
set WkEndFlg=(select case DAYOFWEEK(startDay) when '1' then 1 else 0 end ); #12.周末标志
set TenDyEndFlg=(select case DAYOFMONTH(startDay) when '10' then 1
when '20' then 1
when DAYOFMONTH(LAST_DAY(startDay)) then 1
else 0 end ); #13.旬末标志
set MoEndFlg=(select case DAYOFMONTH(startDay) when DAYOFMONTH(LAST_DAY(startDay)) then 1 else 0 end ); #14.月末标志
set QuaEndFlg=(select case date(startDay)
when concat(year(startDay),'0331') then 1
when concat(year(startDay),'0630') then 1
when concat(year(startDay),'0930') then 1
when concat(year(startDay),'1231') then 1
else 0 end ); #15.季末标志
set YrEndFlg=(select case date(startDay) when concat(year(startDay),'1231') then 1 else 0 end ); #16.年末标志
set WkBgnDt=(select DATE_FORMAT(date_sub(startDay,INTERVAL WEEKDAY(startDay) DAY),'%Y%m%d')); #17.周初日期
set BgnTenDyDt=(select DATE_FORMAT( case
when DAYOFMONTH(startDay)<11 then concat(substr(date(startDay),1,8),'01')
when 11<=DAYOFMONTH(startDay) and DAYOFMONTH(startDay)<21 then concat(substr(date(startDay),1,8),'11')
when 21<=DAYOFMONTH(startDay) then concat(substr(date(startDay),1,8),'21')
else 0 end ,'%Y%m%d')); #18.旬初日期
set MoBgnDt=(SELECT date_format(LAST_DAY(startDay),'%Y%m01') ); #19.月初日期
set QuaBgnDt=(select DATE_FORMAT(concat(year(startDay),
case when (floor(substr(startDay,6,2)/3.1)*3)+1<10 then concat(0,(floor(substr(startDay,6,2)/3.1)*3)+1)
else (floor(substr(startDay,6,2)/3.1)*3)+1 end,'01'),'%Y%m%d') ); #20.季初日期
set YrBgnDt=(SELECT DATE_FORMAT(DATE_SUB(startDay,INTERVAL dayofyear(startDay)-1 DAY),'%Y%m%d')); #21.年初日期
set WkEndDt=(select DATE_FORMAT(date_sub(startDay,INTERVAL WEEKDAY(startDay) - 6 DAY),'%Y%m%d')); #22.周末日期
set TenDyEndDt=(select DATE_FORMAT(case
when DAYOFMONTH(startDay)<11 then concat(substr(date(startDay),1,8),'10')
when 11<=DAYOFMONTH(startDay) and DAYOFMONTH(startDay)<21 then concat(substr(date(startDay),1,8),'20')
when 21<=DAYOFMONTH(startDay) then last_day(startDay)
else 0 end,'%Y%m%d') ); #23.旬末日期
set MoEndDt=(SELECT DATE_FORMAT(LAST_DAY(startDay),'%Y%m%d')); #24.月末日期
set QuaEndDt=(select DATE_FORMAT(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM startDay),1) + interval QUARTER(startDay)*3-1 month),'%Y%m%d') ); #25.季末日期
set YrEndDt=(select concat(year(startDay),'1231')); #26.年末日期
set LastWkEndDt=(select DATE_FORMAT(date_sub(startDay,INTERVAL WEEKDAY(startDay) + 1 DAY),'%Y%m%d')); #27.上周末日期
set LastTenDyEndDt=(select DATE_FORMAT(case
when DAYOFMONTH(startDay)<11 then last_day(date_add(startDay,interval-1 month))
when 11<=DAYOFMONTH(startDay) and DAYOFMONTH(startDay)<21 then concat(substr(date(startDay),1,8),'10')
when 21<=DAYOFMONTH(startDay) then concat(substr(date(startDay),1,8),'20')
else 0 end,'%Y%m%d') ); #28.上旬末日期
set LastMoEndDt=(select DATE_FORMAT(last_day(date_sub(startDay,interval 1 month)),'%Y%m%d') ); #29.上月末日期
set LastQuaEndDt=(select DATE_FORMAT(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM startDay),1) + interval QUARTER(startDay)*3-4 month),'%Y%m%d')); #30.上季末日期
set LastYrEndDt=(select concat(year(date(date_add(startDay,interval-1 year))),'1231') ); #31.上年末日期
SET LclMoLastTenDyDt =(select concat(year(startDay),substr(startDay,6,2),'10')); #32.本月上旬日期
SET LclMoMidTenDyDt =(select concat(year(startDay),substr(startDay,6,2),'20')); #33.本月中旬日期
SET LclMoNxtTenDyDt =(select concat(year(startDay),substr(startDay,6,2),substr(DATE_FORMAT(LAST_DAY(startDay),'%Y%m%d'),7,2))); #34.本月下旬日期
SET LastAnul = (select year(date(date_add(startDay,interval-1 year)))); #35.上年度
set Anul=(select year(startDay)); #36.年度
set Qtr=(select QUARTER(startDay)); #37.季度
set Mo=(select MONTH(startDay) ); #38.月份
set CrnDy = (select substr(startDay,9,2)); #39.当日
set Wk = DAYOFWEEK(DATE_SUB(startDay,INTERVAL 1 day)); #40.周几
set hfyrBgn = (select case when MONTH(startDay) in(1,2,3,4,5,6) then concat(year(startDay),'0101')
else concat(year(startDay),'0701') end); #41.半年初
set hfyrEnd = (select case when MONTH(startDay) in(1,2,3,4,5,6) then concat(year(startDay),'0630')
else concat(year(startDay),'1231') end); #42.半年末
set WkNuInDay=(SELECT DAYOFWEEK(DATE_SUB(startDay,INTERVAL 1 day))); #43.星期中第几天
set MoNuInDay=(select DAYOFMONTH(startDay)); #44.月中第几天
set QtrNuInDay = (select DAYOFYEAR(startDay) - DAYOFYEAR(DATE_FORMAT(concat(year(startDay),
case when (floor(substr(startDay,6,2)/3.1)*3)+1<10 then concat(0,(floor(substr(startDay,6,2)/3.1)*3)+1)
else (floor(substr(startDay,6,2)/3.1)*3)+1 end,'01'),'%Y%m%d'))+1); #45.季度中第几天
set hfyrNuInDay = (select case when MONTH(startDay) in(1,2,3,4,5,6) then DAYOFYEAR(startDay)
else DAYOFYEAR(startDay)-DAYOFYEAR(case when MONTH(startDay) in(1,2,3,4,5,6) then concat(year(startDay),'0101')
else concat(year(startDay),'0701') end)+1 end); #46.半年中第几天
set YrNuInDay=(select DAYOFYEAR(startDay) ); #47.年中第几天
set MoNuInIndSevenDay=(SELECT case when DAYOFMONTH(startDay)<=7 then 1
when 7<DAYOFMONTH (startDay) and DAYOFMONTH (startDay)<=14 then 2
when 14<DAYOFMONTH(startDay) and DAYOFMONTH (startDay)<=21 then 3
when 21<DAYOFMONTH(startDay) and DAYOFMONTH (startDay)<=28 then 4
when 28<DAYOFMONTH(startDay) and DAYOFMONTH (startDay)<=31 then 5
else 0 end); #48.月中第几个七天
set MoNuInWk=(select case when substring(date_add(startDay,interval -WEEKDAY(startDay) day),6,2) != substring(startDay,6,2)
then (DAY(date_add(startDay,interval -WEEKDAY(startDay) day))+WEEKDAY(date_add(startDay,interval -WEEKDAY(startDay) day)-INTERVAL DAY(date_add(startDay,interval -WEEKDAY(startDay) day)) DAY)) DIV 7
else (DAY(startDay)+WEEKDAY(startDay-INTERVAL DAY(startDay) DAY)) DIV 7 end ); #49.月中第几星期
set YrNuInWk=(select week(startDay,7)); #50.年中第几星期
set QtrNuInMo=(select case when MONTH(startDay) in(1,4,7,10) then '1'
when MONTH(startDay) in(2,5,8,11) then '2'
else '3' end ); #51.季度中第几月
set YrNuInMo=(select MONTH(startDay)); #52.年中第几月
set YrNuInQtr=(select QUARTER(date(startDay))); #53.年中第几季度
set LastMoSamTrm = (select DATE_FORMAT(date_add(startDay,interval -1 month),'%Y%m%d')); #54.上月同期
set LastMoFinSamTrm = (select DATE_FORMAT(last_day(date_sub(startDay,interval 1 month)),'%Y%m%d')); #55.上月财务同期
set LastYrSamTrm = (select DATE_FORMAT(DATE_ADD(startDay, INTERVAL -1 YEAR ),'%Y%m%d')); #56.上年同期
set LastYrFinSamTrm = (select DATE_FORMAT(DATE_ADD(last_day(startDay), INTERVAL -1 YEAR ),'%Y%m%d')); #57.上年财务同期
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
5
0- 0
已为社区贡献1条内容
所有评论(0)