How do I get the month and year for a date varchar on MySQL ?
Example:
31/01/2013
31/02/2013
01/03/2013
01/01/2014
I want only dates 01/2013 in case of 31/01/2013
How to get month and year from date?
1
asked by anonymous 11.08.2016 / 15:37
3 answers
3
If your date field is of type varchar , you must first convert it to date format using the STR_TO_DATE , then you can use the YEAR and MONTH :
Combining the functions 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 on sqlfiddle
11.08.2016 / 16:17
1
If the dates are in fields of type date or datetime , you can use YEAR and MONTH :
SELECT * FROM SUA_TABELA where year(CAMPODATA) = 2013 and month(CAMPODATA) = 1;
11.08.2016 / 15:49
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
11.08.2016 / 15:58