Getting first day of the week in MySql using Week No

Question

Hi

How do I get the first day of a given week whose week number is available?

For example as I write this post we are at WEEK 29.I would like to write a MySQL query that will return Sunday 18 July using this WEEKNO 29 as the only available parameter.

Answer 1

Untested (I don't have MySQL at hand):

date_add(
   date_sub(curdate(), interval weekday(curdate()) day),
   interval ((NUM-weekofyear(curdate()))*7) day)

Answer 2

You can use:

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

This would give you the Monday date of week 3 of 2010, which would be 2010-01-18.

Another example:

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

Would give you the Sunday date of week 52 of 2010, which would be 2010-12-26.

And finally, using your original example:

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

This gives 2010-07-18.