teratail
回答率
85.47%
質問するログイン新規登録
トップMySQLに関する質問[MySQL] date_format関数で%uを用いた時に、週頭の日付を取得する方法

編集履歴

回答編集履歴

5

追加

2017/06/12 01:04

投稿

A.Ichi
A.Ichi

スコア4070

test CHANGED
@@ -7,3 +7,21 @@
7
7
  select date_add(date_sub(@e,interval weekday(@e) day), interval (date_format(@d,'%u'))*7 day) from (SELECT @d:=@x,@e:=date_format(@d,'%Y0101')) t1;
8
8
 
9
9
  ```
10
+
11
+ 2017年の23週の月曜日
12
+
13
+ ```sql
14
+
15
+ set @y:=2017;
16
+
17
+ set @x:=23;
18
+
19
+
20
+
21
+ select date_add(date_sub(@e,interval weekday(@e) day), interval @x*7 day) from (SELECT @e:=concat(@y,'0101')) t1;
22
+
23
+ ```
24
+
25
+
26
+
27
+

4

訂正

2017/06/12 01:04

投稿

A.Ichi
A.Ichi

スコア4070

test CHANGED
@@ -4,6 +4,6 @@
4
4
 
5
5
  set @x:=20170611;
6
6
 
7
- select date_add(makedate(year(@d),case when @n=8 then 1 else @n end), interval (date_format(@d,'%u')-1)*7 day) from (SELECT @d:=@x,@n:=8-weekday(date_format(@d,'%Y0101'))) t1;
7
+ select date_add(date_sub(@e,interval weekday(@e) day), interval (date_format(@d,'%u'))*7 day) from (SELECT @d:=@x,@e:=date_format(@d,'%Y0101')) t1;
8
8
 
9
9
  ```

3

変更

2017/06/11 22:24

投稿

A.Ichi
A.Ichi

スコア4070

test CHANGED
@@ -4,6 +4,6 @@
4
4
 
5
5
  set @x:=20170611;
6
6
 
7
- select makedate(year(@d),case when @n=8 then 1 else @n end + (date_format(@d,'%u')-1) * 7) from (SELECT @d:=@x,@n:=8-weekday(date_format(@d,'%Y0101'))) t1;
7
+ select date_add(makedate(year(@d),case when @n=8 then 1 else @n end), interval (date_format(@d,'%u')-1)*7 day) from (SELECT @d:=@x,@n:=8-weekday(date_format(@d,'%Y0101'))) t1;
8
8
 
9
9
  ```

2

変更

2017/06/11 15:35

投稿

A.Ichi
A.Ichi

スコア4070

test CHANGED
@@ -2,6 +2,8 @@
2
2
 
3
3
  ```
4
4
 
5
+ set @x:=20170611;
6
+
5
- select makedate(year(@d),case when @n=8 then 1 else @n end + (date_format(@d,'%u')-1) * 7) from (SELECT @d:='20170611',@n:=8-weekday(date_format(@d,'%Y0101'))) t1;
7
+ select makedate(year(@d),case when @n=8 then 1 else @n end + (date_format(@d,'%u')-1) * 7) from (SELECT @d:=@x,@n:=8-weekday(date_format(@d,'%Y0101'))) t1;
6
8
 
7
9
  ```

1

修正

2017/06/11 15:12

投稿

A.Ichi
A.Ichi

スコア4070

test CHANGED
@@ -2,6 +2,6 @@
2
2
 
3
3
  ```
4
4
 
5
- select date_sub(@d,interval weekday(date_add(date_format(@d,'%Y0101'),interval date_format(@d,'%u')*7 day)) day) from (SELECT @d:='20170611') t1;
5
+ select makedate(year(@d),case when @n=8 then 1 else @n end + (date_format(@d,'%u')-1) * 7) from (SELECT @d:='20170611',@n:=8-weekday(date_format(@d,'%Y0101'))) t1;
6
6
 
7
7
  ```