其中 startDay 格式为 = ‘-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 1when concat(year(startDay),'0401') then 1when concat(year(startDay),'0701') then 1when concat(year(startDay),'1001') then 1else 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 1when concat(year(startDay),'0630') then 1when concat(year(startDay),'0930') then 1when concat(year(startDay),'1231') then 1else 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.本月上旬日期SETLclMoMidTenDyDt =(select concat(year(startDay),substr(startDay,6,2),'20'));#33.本月中旬日期SETLclMoNxtTenDyDt =(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 2when 14<DAYOFMONTH(startDay) and DAYOFMONTH (startDay)<=21 then 3when 21<DAYOFMONTH(startDay) and DAYOFMONTH (startDay)<=28 then 4when 28<DAYOFMONTH(startDay) and DAYOFMONTH (startDay)<=31 then 5else 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 7else (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.上年财务同期
