Get date with day of week, number of week, month and year

From the year, week of the year (1..53) and the day in full it is possible to determine the date in the format YYYY-mm-dd,

%X - Ano para semana com quatro digitos, sendo o primeiro dia da semana Sunday(domingo).
%V - Semana do ano.
%W - Dia da semana por extenso.

SELECT str_to_date('201437 Monday', '%X%V %W')

output:

2014-09-15

Parameters available for formatting str_to_date and date_format

Example

Mysql has a lot of functions to handle dates , it's worth reading the documentation . You can use the YEAR() functions for year, MONTH() for Month, WEEK() for week and DAYNAME() for day name, for example:

    SET lc_time_names = 'pt_BR';
    SET @data = "2014/09/18";
    SELECT 
        YEAR(@data) AS Ano, 
        MONTH(@data) AS Mês,
        WEEK(@data) AS Semana, 
        DAYNAME(@data) AS Dia_da_Semena 

You can also "spike" the string with the date in the call of each of these functions. The disadvantage is that every time you need to change the date, you need to make the change everywhere, as follows:

    SET lc_time_names = 'pt_BR';
    SELECT 
        YEAR("2014/09/18") AS Ano,
        WEEK("2014/09/18") AS Semana, 
        MONTH("2014/09/18") AS Mês, 
        DAYNAME("2014/09/18") AS Dia_da_Semana

NOTE: I needed to add SET lc_time_names = 'pt_BR' because my MySQL is in English.

SET lc_time_names = 'pt_BR';