2017年8月22日火曜日

MySQLで日付から「○月第○週」を求める方法

MySQLで日付から「○月第○週」を求める方法のメモ。

-- テスト用テーブル・データ作成
create table test.weektest (date1 date primary key);

insert into test.weektest values('2017/06/01');
insert into test.weektest values('2017/06/02');
insert into test.weektest values('2017/06/03');
insert into test.weektest values('2017/06/04');
insert into test.weektest values('2017/06/05');
insert into test.weektest values('2017/06/06');
insert into test.weektest values('2017/06/07');
insert into test.weektest values('2017/06/08');
insert into test.weektest values('2017/06/09');
insert into test.weektest values('2017/06/10');
insert into test.weektest values('2017/06/11');
insert into test.weektest values('2017/06/12');
insert into test.weektest values('2017/06/13');
insert into test.weektest values('2017/06/14');
insert into test.weektest values('2017/06/15');
insert into test.weektest values('2017/06/16');
insert into test.weektest values('2017/06/17');
insert into test.weektest values('2017/06/18');
insert into test.weektest values('2017/06/19');
insert into test.weektest values('2017/06/20');
insert into test.weektest values('2017/06/21');
insert into test.weektest values('2017/06/22');
insert into test.weektest values('2017/06/23');
insert into test.weektest values('2017/06/24');
insert into test.weektest values('2017/06/25');
insert into test.weektest values('2017/06/26');
insert into test.weektest values('2017/06/27');
insert into test.weektest values('2017/06/28');
insert into test.weektest values('2017/06/29');
insert into test.weektest values('2017/06/30');
insert into test.weektest values('2017/07/01');
insert into test.weektest values('2017/07/02');
insert into test.weektest values('2017/07/03');
insert into test.weektest values('2017/07/04');
insert into test.weektest values('2017/07/05');
insert into test.weektest values('2017/07/06');
insert into test.weektest values('2017/07/07');
insert into test.weektest values('2017/07/08');
insert into test.weektest values('2017/07/09');
insert into test.weektest values('2017/07/10');
insert into test.weektest values('2017/07/11');
insert into test.weektest values('2017/07/12');
insert into test.weektest values('2017/07/13');
insert into test.weektest values('2017/07/14');
insert into test.weektest values('2017/07/15');
insert into test.weektest values('2017/07/16');
insert into test.weektest values('2017/07/17');
insert into test.weektest values('2017/07/18');
insert into test.weektest values('2017/07/19');
insert into test.weektest values('2017/07/20');
insert into test.weektest values('2017/07/21');
insert into test.weektest values('2017/07/22');
insert into test.weektest values('2017/07/23');
insert into test.weektest values('2017/07/24');
insert into test.weektest values('2017/07/25');
insert into test.weektest values('2017/07/26');
insert into test.weektest values('2017/07/27');
insert into test.weektest values('2017/07/28');
insert into test.weektest values('2017/07/29');
insert into test.weektest values('2017/07/30');
insert into test.weektest values('2017/07/31');
insert into test.weektest values('2017/08/01');
insert into test.weektest values('2017/08/02');
insert into test.weektest values('2017/08/03');
insert into test.weektest values('2017/08/04');
insert into test.weektest values('2017/08/05');
insert into test.weektest values('2017/08/06');
insert into test.weektest values('2017/08/07');
insert into test.weektest values('2017/08/08');
insert into test.weektest values('2017/08/09');
insert into test.weektest values('2017/08/10');

SELECT
date1
,CONCAT(MONTH(ADDDATE(date1, weekday(date1) * -1))
  , '月第'
  , FLOOR((DAYOFMONTH(ADDDATE(date1, weekday(date1) * -1))-1)/7)+1, '週') 
AS WEEK
FROM test.weektest
ORDER BY date1;

↓のような結果が得られる。月曜始まりとして、ある日付の週の月曜日がある月の第○週を返す。

date1 WEEK
2017/06/01 5月第5週
2017/06/02 5月第5週
2017/06/03 5月第5週
2017/06/04 5月第5週
2017/06/05 6月第1週
2017/06/06 6月第1週
2017/06/07 6月第1週
2017/06/08 6月第1週
2017/06/09 6月第1週
2017/06/10 6月第1週
2017/06/11 6月第1週
2017/06/12 6月第2週
2017/06/13 6月第2週
2017/06/14 6月第2週
2017/06/15 6月第2週
2017/06/16 6月第2週
2017/06/17 6月第2週
2017/06/18 6月第2週
2017/06/19 6月第3週
2017/06/20 6月第3週
2017/06/21 6月第3週
2017/06/22 6月第3週
2017/06/23 6月第3週
2017/06/24 6月第3週
2017/06/25 6月第3週
2017/06/26 6月第4週
2017/06/27 6月第4週
2017/06/28 6月第4週
2017/06/29 6月第4週
2017/06/30 6月第4週
2017/07/01 6月第4週
2017/07/02 6月第4週
2017/07/03 7月第1週
2017/07/04 7月第1週
2017/07/05 7月第1週
2017/07/06 7月第1週
2017/07/07 7月第1週
2017/07/08 7月第1週
2017/07/09 7月第1週
2017/07/10 7月第2週
2017/07/11 7月第2週
2017/07/12 7月第2週
2017/07/13 7月第2週
2017/07/14 7月第2週
2017/07/15 7月第2週
2017/07/16 7月第2週
2017/07/17 7月第3週
2017/07/18 7月第3週
2017/07/19 7月第3週
2017/07/20 7月第3週
2017/07/21 7月第3週
2017/07/22 7月第3週
2017/07/23 7月第3週
2017/07/24 7月第4週
2017/07/25 7月第4週
2017/07/26 7月第4週
2017/07/27 7月第4週
2017/07/28 7月第4週
2017/07/29 7月第4週
2017/07/30 7月第4週
2017/07/31 7月第5週
2017/08/01 7月第5週
2017/08/02 7月第5週
2017/08/03 7月第5週
2017/08/04 7月第5週
2017/08/05 7月第5週
2017/08/06 7月第5週
2017/08/07 8月第1週
2017/08/08 8月第1週
2017/08/09 8月第1週
2017/08/10 8月第1週



0 件のコメント:

コメントを投稿