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

Asked

Viewed 10,731 times

1

Is there any way to get the date if you only have the day of the week, the number of week, month and year with Mysql?

Example:

I want to know what day it is with these parameters:

  • Year: 2014
  • Month: September (09)
  • Number of week of year: 37 or Week number in September: 3
  • Day of the week: Thursday

The answer is "2014/09/18"

mysql sql date

2 answers

6


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

%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')

exit:

2014-09-15

Parameters available for formatting str_to_date and date_format

Example

  • It worked great here, I’m just having a little problem when the week is the first of January, it returns null

  • I did it, I tweaked the parameters to format and it looked like this: SELECT str_to_date('201400 Wednesday', '%Y%U %W') and it worked

3

Mysql has a large amount of functions to handle dates, is worth a read on documentation. You can use the functions YEAR() next year, MONTH() for the month, WEEK() next week and DAYNAME() for the name of the day, 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 "insert" the string with the date in the call of each of these functions. The disadvantage will be that every time you need to change the date, you will need to make the change everywhere, 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';

Browser other questions tagged mysql sql date

You are not signed in. Login or sign up in order to post.