I'm trying to extract month from date which looks like this "2008-08-17 00:00:00"-CodePudding

This is an example,Im trying combine two tables here which are db_match and country as you can see but I need to extract just month from the whole date so what could I try here ?

I tried,

update db_match set date=str_to_date(date,"%Y/%m/%d %h:%i:%s");

here's the join query:

select db_match.date,country.name,country.id
from db_match(
select EXTRACT (MONTH FROM date) as Themonth
)sub
inner join country
on country.id=db_match.country_id

group by Themonth
order by id;

CodePudding user response：

Use MONTH()
NB You have tagged mySQL and this works in mySQL? I don't now qsqldatabase.

SELECT MONTH('2008-08-17 00:00:00') "month"

returns 8
Your query becomes something like the following. I can't test it without your table descriptions and sample data.

SELECT
  MONTH(d.date) "Month",
  c.name Country,
  c.id Code
FROM 
  db_match d 
JOIN
  county c on d.country_id = c.id
GROUP BY 
  MONTH(d.date),
  c.name,
  c.id;