CODEWITHSUNDEEP
mysql
Generalbet
Generalbet

Reputation: 47

MYSQL Date of monday in given week and year, mode 7

I'm trying to get the date of the first Monday by given week number and year.

I found this code: SELECT STR_TO_DATE(CONCAT('20131',' Monday'), '%x%v %W') as MQK;

It works in most cases, following mode 3, but I want it in mode 7. Date and Time Functions

For example, if the year is 2013 and week 1, it gives:

mysql> SELECT STR_TO_DATE(CONCAT('20131',' Monday'), '%x%v %W') as first_day;
+------------+
| first_day  |
+------------+
| 2012-12-31 |
+------------+
1 row in set (0.00 sec)

I'm expecting date 2013-01-07.

Is it possible?

Upvotes: 2

Views: 135

Answers (1)

dossy
dossy

Reputation: 1679

You would have to implement a conditional to emulate the mode 7 behavior.

SELECT @y := 2013 as `@y`, @w := 1 as `@w`,
       @d := STR_TO_DATE(CONCAT(@y, @w, ' Monday'), '%x%v %W') as `@d`,
       IF (YEAR(@d) < @y, DATE_ADD(@d, INTERVAL 1 WEEK), @d) as first_day;

You would get:

+------+----+------------+------------+
| @y   | @w | @d         | first_day  |
+------+----+------------+------------+
| 2013 |  1 | 2012-12-31 | 2013-01-07 |
+------+----+------------+------------+

And, in 2007 where Jan 1 is the first Monday of the year:

SELECT @y := 2007 as `@y`, @w := 1 as `@w`,
       @d := STR_TO_DATE(CONCAT(@y, @w, ' Monday'), '%x%v %W') as `@d`,
       IF (YEAR(@d) < @y, DATE_ADD(@d, INTERVAL 1 WEEK), @d) as first_day;

+------+----+------------+------------+
| @y   | @w | @d         | first_day  |
+------+----+------------+------------+
| 2007 |  1 | 2007-01-01 | 2007-01-01 |
+------+----+------------+------------+

Added 2018-10-25:

So, apparently this doesn't work for 2013 week 2, which using the above query results in 2013-01-07 which is actually the Monday for week 1. In hindsight, this was kind of obvious based on the approach I originally proposed and the obvious solution just jumped out at me.

SELECT @y := 2013 as `@y`, @w := 1 as `@w`,
       @d := STR_TO_DATE(CONCAT(@y, @w, ' Monday'), '%x%v %W') as `@d`,
       IF (WEEK(@d, 7) <> @w, DATE_ADD(@d, INTERVAL 1 WEEK), @d) as first_day;

+------+----+------------+------------+
| @y   | @w | @d         | first_day  |
+------+----+------------+------------+
| 2013 |  1 | 2012-12-31 | 2013-01-07 |
+------+----+------------+------------+

SELECT @y := 2013 as `@y`, @w := 2 as `@w`,
       @d := STR_TO_DATE(CONCAT(@y, @w, ' Monday'), '%x%v %W') as `@d`,
       IF (WEEK(@d, 7) <> @w, DATE_ADD(@d, INTERVAL 1 WEEK), @d) as first_day;

+------+----+------------+------------+
| @y   | @w | @d         | first_day  |
+------+----+------------+------------+
| 2013 |  2 | 2013-01-07 | 2013-01-14 |
+------+----+------------+------------+

Heh.

Upvotes: 3

Related Questions