MySQL convert YEARWEEK to date

Question

I've a YEARWEEK() converted value from MySQL

201439

I want to convert it back to the starting date of that week number, using MySQL, possible?

e.g.

SELECT xxx('201439');

and return

'2014-09-16'

Answer 1

You need to be careful that the yearweek is in the expected "mode". (See https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_week)

The formatting used in the STR_TO_DATE should match. (See https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format)

e.g. If using mode 2 (1-53, Week 1 is the first week with a Sunday in it, weeks start on a Sunday)

SELECT STR_TO_DATE(CONCAT('201439',' Sunday'), '%X%V %W');

e.g. If using mode 3 (following ISO-8601), (1-53, Week 1 is the first week with 4 or more days, starting on a Monday), you need to use the lower-case version.

SELECT STR_TO_DATE(CONCAT('201439',' Monday'), '%x%v %W');

So, one would get the following (2014-09-28 is a Sunday):

SELECT yearweek('2014-09-28', 2);
201439
SELECT yearweek('2014-09-28', 3);
201439
SELECT yearweek('2014-09-29', 2);
201439
SELECT yearweek('2014-09-29', 3);
201440

then

SELECT STR_TO_DATE(CONCAT('201439',' Sunday'), '%X%V %W'); -- mode 2
2014-09-28
SELECT STR_TO_DATE(CONCAT('201439',' Monday'), '%x%v %W'); -- mode 3 
2014-09-22
SELECT STR_TO_DATE(CONCAT('201440',' Monday'), '%x%v %W'); -- mode 3
2014-09-29

Answer 2

You can use STR_TO_DATE function with format you need

http://sqlfiddle.com/#!9/9eecb7d/2017

SELECT STR_TO_DATE(CONCAT(YEARWEEK(NOW()),' Monday'), '%X%V %W');

SELECT STR_TO_DATE(CONCAT('201439',' Monday'), '%X%V %W');