英文月份转为数字

提示你一下吧 具体的就不帮你编了 程序这个东西要多编才会熟练的 用switch语句就可以实现这个功能了 至于细节部分 自己考虑吧 还有注意 英文月份名是字符串 就提醒这么多了

用什么语言啊！？

日期变换：(1)dt转日期to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd')))(2)日期转dtregexp_replace('${date}','-','')(3)dt转当月1号日期to_date(from_unixtime(unix_timestamp(concat(substr('${dt}',1,6),'01'),'yyyyMMdd')))trunc(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))),'MM')-- 下月1号日期trunc(add_months(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))),1),'MM')(4)dt转当周星期一日期next_day(date_add(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))), -7), 'Mo')date_sub(next_day(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))),'MO'),7)-- 下周星期一日期next_day(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))),'MO')(5)dt前六天日期(dt为星期天时得到的是本周周一的日期)date_add(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))), -6)(5)dt转当季第一天日期if(length(floor(substr('${dt}',5,2)/3.1)*3+1)=1,concat(substr('${dt}',1,4),'-0',floor(substr('${dt}',5,2)/3.1)*3+1,'-01'),concat(substr('${dt}',1,4),'-',floor(substr('${dt}',5,2)/3.1)*3+1,'-01'))(6)dt转半年第一天日期if(length(floor(substr('${dt}',5,2)/6.1)*6+1)=1,concat(substr('${dt}',1,4),'-0',floor(substr('${dt}',5,2)/6.1)*6+1,'-01'),concat(substr('${dt}',1,4),'-',floor(substr('${dt}',5,2)/6.1)*6+1,'-01'))(7)dt转当年1号日期concat(substr('${dt}',1,4),'-01-01')(8)在同时有日周月粒度时要注意数据的时间范围，有时每月的第一个自然周会跨月，比如2019年3月的第一周的日期是20190225-20190303where agent_business_date between date_add_day('${dt}',-31) and to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd')))where dt between regexp_replace(date_add_day('${dt}',-31),'-','') and '${dt}'-------------------------------------------------------------------------------------------- 日期维度表表结构edw_public.dim_esf_edw_pub_date------------------------------------------------------------------------------------------col_name data_type comment------------------------------------------------------------------------calendar_date string 日期，格式为"YYYY-MM-DD"week_english_name string 星期英文名week_chinese_name string 星期中文名day_of_week_number int 所属一周当中的第几天calendar_month_code string 日期所属月份，格式为"YYYY-MM"calendar_month_number int 所属月份数字month_english_name string 月份英文名month_chinese_name string 月份中文名day_of_month_number int 所属月份当中的第几天calendar_quater_code string 日期所属季度，格式为"YYYY-QT"calendar_quater_number int 所属季度数字day_of_quater_number int 所属季度当中的第几天calendar_half_year_code string 日期所属半年，格式为"YYYY-HY"calendar_half_year_number int 所属半年数字，1为上半年，2为下半年calendar_year_code string 日期所属年份，格式为"YYYY"day_of_year_number int 所属年份当中的第几天work_day_flag string 工作日标志: Y - 是/ N - 否holiday_flag string 节假日标志: Y - 是/ N - 否-- 日期维度表的使用-- 当天日期SELECTcalendar_dateFROMedw_public.dim_esf_edw_pub_dateWHEREcalendar_date = regexp_replace('${dt}','(\\d{4})(\\d{2})(\\d{2})','$1-$2-$3')-- Finereport中日周月季半年年 各周期末日期的算法select${if(粒度 == 1," case when date(max(calendar_date))>=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date","")}${if(粒度 == 2," distinct case when day_of_week_number = 1 and date_add('day',6,date(calendar_date)) >=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) when day_of_week_number = 7 and date(calendar_date) >=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) when day_of_week_number = 1 then date_add('day',6,date(calendar_date)) when day_of_week_number = 7 then date(calendar_date) else date(calendar_date) end as period_end_date ","")}${if(粒度 == 3," case when date(max(calendar_date))>=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date ","")}${if(粒度 == 4," case when date(max(calendar_date))>=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date ","")}${if(粒度 == 5," case when date(max(calendar_date))>=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date ","")}${if(粒度 == 6," case when date(max(calendar_date))>=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date ","")}fromedw_public.dim_esf_edw_pub_datewhere calendar_date >= '${开始时间}' and calendar_date <= '${结束时间}'${if(粒度 == 1," group by calendar_date ","")}${if(粒度 == 2," and day_of_week_number in (1,7) ","")}${if(粒度 == 3," group by calendar_month_code ","")}${if(粒度 == 4," group by calendar_quater_code ","")}${if(粒度 == 5," group by calendar_year_code ","")}${if(粒度 == 6," group by calendar_half_year_code ","")}-- Finereport中日周月季半年年 各周期期初期末日期的算法(这种计算方法当前日期是20190330，输入的日期范围是2019-03-01至2091-03-28则输出的月日期范围是2019-03-29)select${if(粒度 == 1,"date(calendar_date) as period_start_date, date(calendar_date) as period_end_date ","")}${if(粒度 == 2,"case when day_of_week_number = 1 then date(calendar_date) when day_of_week_number = 7 then date_add('day',-6, date(calendar_date)) end as period_start_date, case when day_of_week_number = 1 and date_add('day',6, date(calendar_date)) >=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) when day_of_week_number = 7 and date(calendar_date)>=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) when day_of_week_number = 1 then date_add('day',6, date(calendar_date)) when day_of_week_number = 7 then date(calendar_date) end as period_end_date ","")}${if(粒度 == 3,"date(calendar_date) as period_start_date, case when date_add('day',-day(date(calendar_date)),date_add('month',1,(date(calendar_date))))>=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date_add('day',-day(date(calendar_date)),date_add('month',1,(date(calendar_date)))) end as period_end_date ","")}${if(粒度 == 4,"calendar_date as period_start_date,date_add('day',-1,date_add('month',1,date(substr(calendar_date,1,4)||'-'||cast(cast(floor(cast(substr(calendar_date,6,2) as int)/3.1)*3+3 as int) as varchar)||'-01'))) as period_end_date ","")}${if(粒度 == 5,"date(concat(substr(calendar_date,1,4),'-01','-01')) as period_start_date,case when date(concat(substr(calendar_date,1,4),'-12','-31'))>= date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(concat(substr(calendar_date,1,4),'-12','-31')) end as period_end_date","")}${if(粒度 == 6,"date(min(calendar_date)) as period_start_date,case when date(max(calendar_date))>= date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date","")}fromedw_public.dim_esf_edw_pub_datewhere calendar_date >= '${开始时间}' and calendar_date <= '${结束时间}'${if(粒度 == 1," and 1 = 1 ","")}${if(粒度 == 2," and day_of_week_number in (1,7) ","")}${if(粒度 == 3," and day_of_month_number = 1","")}${if(粒度 == 4," and day_of_quater_number = 1","")}${if(粒度 == 5," and day_of_year_number = 1","")}${if(粒度 == 6," group by calendar_half_year_code ","")}-------------------------------------------------------------------------------------------------- 根据输入的时间范围计算期末日期------------------------------------------------------------------------------------------------select t1.*from-- 日周月季年半年不同粒度的统计数据各存为了一张表edw_reports.adm_xf_edw_house_sub_project_report_00${dtype}ly_di t1--日报join(-- 日SELECTcalendar_dateFROMedw_public.dim_esf_edw_pub_dateWHEREcalendar_date BETWEEN '${bdt}' AND '${edt}'AND '${dtype}' = '1_dai'UNION-- 月SELECTMAX(calendar_date) AS calendar_dateFROMedw_public.dim_esf_edw_pub_dateWHEREcalendar_date BETWEEN '${bdt}' AND '${edt}'AND '${dtype}' = '2_dai'GROUP BYcalendar_month_numberUNION-- 周SELECTcalendar_dateFROMedw_public.dim_esf_edw_pub_dateWHEREcalendar_date BETWEEN '${bdt}' AND '${edt}'AND day_of_week_number = 7AND '${dtype}' = '3_dai'UNION-- 季SELECTMAX(calendar_date) AS calendar_dateFROMedw_public.dim_esf_edw_pub_dateWHEREcalendar_date BETWEEN '${bdt}' AND '${edt}'AND '${dtype}' = '4_dai'GROUP BYcalendar_quater_codeUNION-- 年SELECTMAX(calendar_date) AS calendar_dateFROMedw_public.dim_esf_edw_pub_dateWHEREcalendar_date BETWEEN '${bdt}' AND '${edt}'AND '${dtype}' = '5_dai'GROUP BYcalendar_year_codeUNION-- 半年SELECTMAX(calendar_date) AS calendar_dateFROMedw_public.dim_esf_edw_pub_dateWHEREcalendar_date BETWEEN '${bdt}' AND '${edt}'AND '${dtype}' = '6_dai'GROUP BYcalendar_half_year_codeUNIONSELECTMAX(calendar_date) AS calendar_dateFROMedw_public.dim_esf_edw_pub_dateWHEREcalendar_date BETWEEN '${bdt}' AND '${edt}'ORDER BYcalendar_date) t2on t1.statistic_date = t2.calendar_datewherestatistic_date between '${bdt}' and '${edt}'${if(len(tenant_name) == 0,"","and house_sub_project_organization_short_name = '" + tenant_name + "'")}${if(len(status) == 0,"","and house_sub_project_cooperation_status_code = " + status)}${if(len(tenant_type) == 0,"","and house_sub_project_organization_business_type_code= " + tenant_type)}${if(len(project_type) == 0,"","and house_sub_project_cooperation_type_code= " + project_type)}order by statistic_date

假设英文月份全拼或前3个字母的简拼

在B1中输入或复制粘贴下列公式

=MONTH(A1&"-1")

下拉填充（如下图）

反之

在B1中输入或复制粘贴下列公式

=TEXT(A1&"-1","mmm")

或

=TEXT(A1&"-1","mmmm")

下拉填充（如下图）