MySQL converts YEARWEEK to date

Question

I have a converted YEARWEEK () value from MySQL

201439

I want to convert it back to the start date of this week number using MySQL, maybe?

eg.

SELECT xxx('201439');

and return

'2014-09-16'

Answer 1

You can use the function STR_TO_DATEwith 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');

Answer 2

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

, STR_TO_DATE, . (. https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format)

. 2 (1-53, 1 - , )

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

. 3 ( ISO-8601), (1-53, 1 - 4 , ), .

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

, (2014-09-28 - ):

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

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 3

:

String string = "January 2, 2010";
DateFormat format = new SimpleDateFormat("MMMM d, yyyy", Locale.ENGLISH);
Date date = format.parse(string);

, ! ^^

Answer 4

Try:

SELECT DATE(CONCAT(LEFT('201439', 4), '-01-01')) + INTERVAL RIGHT('201439', 2)-1 WEEK

, , 1 .