年周（年+周）的算法

大家好，欢迎来到IT知识分享网。

select case when weekofyear(p_log_date) < 10 and month(p_log_date) > 11 then (year(p_log_date)+1)*100 + weekofyear(p_log_date) when weekofyear(p_log_date) > 10 and month(p_log_date) < 2 then (year(p_log_date)-1)*100 + weekofyear(p_log_date) else year(p_log_date)*100 + weekofyear(p_log_date) end as year_week 或 select case when weekofyear(p_log_date) < 10 and month(p_log_date) > 11 then concat(year(p_log_date)+1,lpad(weekofyear(p_log_date),2,'0')) when weekofyear(p_log_date) > 10 and month(p_log_date) < 2 then concat(year(p_log_date)-1,lpad(weekofyear(p_log_date),2,'0')) else concat(year(p_log_date),lpad(weekofyear(p_log_date),2,'0')) end as year_week 

举例

select case when weekofyear('2017-01-02') < 10 and month('2017-01-02') > 11 then (year('2017-01-02')+1)*100+weekofyear('2017-01-02') when weekofyear('2017-01-02') > 10 and month('2017-01-02') < 2 then (year('2017-01-02')-1)*100+weekofyear('2017-01-02') else year('2017-01-02')*100+weekofyear('2017-01-02') end as year_week 

（2） Java中的算法

 public static Calendar newCalendar(){ Calendar c = Calendar.getInstance(); c.setFirstDayOfWeek(Calendar.MONDAY); c.setMinimalDaysInFirstWeek(4);//跨年周 周四在哪年 本周属于哪年 return c; } public static int getYearWeek(Date date){ Calendar c = newCalendar(); c.setTime(date); int week = c.get(Calendar.WEEK_OF_YEAR); int month = c.get(Calendar.MONTH)+1; int year = c.get(Calendar.YEAR); if(week<10 && month>11){ return (year+1)*100+week; }else if(week>10 && month<2){ return (year-1)*100+week; }else { return year*100 + week; } } public static void main(String[] args) { Calendar c = Calendar.getInstance(); c.set(Calendar.YEAR, 2016); c.set(Calendar.MONTH, 0); c.set(Calendar.DAY_OF_MONTH, 3); int yearWeek = getYearWeek(c.getTime()); System.out.println(yearWeek); } 

SELECT DATE_FORMAT(NOW(),'%x%v') year_week FROM DUAL