17

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'
Ryan
  • 10,041
  • 27
  • 91
  • 156
  • 1
    Something like `DATE('2014-01-01') + INTERVAL 39 WEEK` – Barmar May 21 '15 at 03:43
  • I get your updated question. My solution is you need to create a very simple function in your source code to parse from **String result** you got from queries to the pattern you want. For example you can use **subString** to cut and fill it to your pattern. :D – ThanhND25 May 21 '15 at 03:55

4 Answers4

31

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
Greg
  • 451
  • 4
  • 6
  • 2
    this is helpful but also confusing and an example including mode 0 (the default mode) would be helpful. – But those new buttons though.. Jan 22 '20 at 18:56
  • And it doesn't always give the correct result. My MySQL has default_week_format=3 so WEEK() gives the correct result as does YEARWEEK() with mode 3. But for week 202053 AND 202101 STR_TO_DATE(CONCAT(202053,' Monday'), '%X%V %W') returns the same result: 2021-01-04.Try this for fun: SELECT @@default_week_format AS mode, YEAR('2021-01-04')*100+WEEK('2021-01-04') AS `WEEK('2021-01-04')`, STR_TO_DATE(CONCAT(202053,' Monday'), '%X%V %W') AS `STR_TO_DATE('202053')`, STR_TO_DATE(CONCAT(202101,' Monday'), '%X%V %W') AS `STR_TO_DATE('202101')`, YEARWEEK('2021-01-04', 3). – Joep Jan 27 '21 at 16:59
14

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');
Alex
  • 16,739
  • 1
  • 28
  • 51
0

Try:

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

This won't get the beginning of the week, it will get the same day of the week as January 1 of that year.

Barmar
  • 741,623
  • 53
  • 500
  • 612
-3

You can use this code likes example: 

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

Hope you like it! ^^

Marwelln
  • 28,492
  • 21
  • 93
  • 117
ThanhND25
  • 86
  • 4