How to get month and year of a date?

Asked

Viewed 16,505 times

1

How I get the month and year of a date varchar in the MySQL?

Example: 

31/01/2013
31/02/2013
01/03/2013
01/01/2014

I only want the dates 01/2013 in the case of 31/01/2013

mysql

3 answers

7


If your date field is of the type varchar, you need to convert it to format first date using the function STR_TO_DATE, then you can use the functions YEAR and MONTH :

Combining the functions, it would be something like:

SELECT YEAR(STR_TO_DATE(data, "%d/%m/%Y")) FROM tabela;

SELECT MONTH(STR_TO_DATE(data, "%d/%m/%Y")) FROM tabela;

Example in sqlfiddle

  • 1

    I did not understand the negative vote, since I objectively answered the problem of the question.

  • worked perfectly

1

If dates are in fields of type date or datetime, may use YEAR and MONTH:

SELECT * FROM SUA_TABELA where year(CAMPODATA) = 2013 and month(CAMPODATA) = 1;
  • 3

    What does the Year and what makes the Month? How do they work? Because Month(CAMPODATA) = 1? If you improve the details, I’ll give you +1 :D

  • -1 For he quoted that the shape of the field, though incorrect, is varchar, your query would not return the expected.

  • @Marcelodeandrade , my answer aimed to guide in the best way, and not necessarily answer the question just to earn points. After all, why someone will use a date in varchar format and lose all resources of date format?

  • The question talks about how to select the month and year, i.e., isolate them from a full date. Your answer does not return the question.

  • @Wallacemaxters Month(CAMPODATA) returns an integer corresponding to the month of the date (example: January = 1, December = 12, etc.), year(campodata) returns the integer corresponding to the year of the date, ex: (2013 for the year 2013, etc.), logo, year(campodata) = 2013 brings all the dates that have the year 2013 and then, filters by month, where Month(campodata) = 1, brings everything from the month of January.

1

Would that be?

select 
    str_to_date('12/31/2011', '%m/%d/%Y') as txtData,
    year(str_to_date('12/31/2011', '%m/%d/%Y')),    
    month(str_to_date('12/31/2011', '%m/%d/%Y'))    
from pedidos    
where   
    year(str_to_date('12/31/2011', '%m/%d/%Y')) = 2011
    and  month(str_to_date('12/31/2011', '%m/%d/%Y'))   = 12

Browser other questions tagged mysql

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