-1

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;
Jens
  • 67,715
  • 15
  • 98
  • 113
supraja
  • 3
  • 1
  • Question is unclear do you want to update the db or create a query joining 2 tables? Also your select query is odd since you have a group by with no aggregate functions. AND the date format in the titles differs from the date format you have used in the question. Please add your table definitions, sample data and desired outcome as text. and review https://stackoverflow.com/help/how-to-ask – P.Salmon Mar 02 '22 at 08:01

1 Answers1

0

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;