SQL Group By - counting records per month/year, error on insert - NOT A VALID MONTH
You can do something like to_date('01/'||trunc(joined), 'DD/MM/YYYY'), which would turn it into a valid date first.You just need to decide whether to use the first or last day of the month (last is more complicated)
Another option is to use the EXTRACT function:
select country, count(*) as members, EXTRACT(MONTH FROM joined) as mn, EXTRACT(YEAR FROM JOINED) as yr,MIN(JOINED) as dtfrom tablegroup by country, EXTRACT(MONTH FROM joined), EXTRACT(YEAR FROM JOINED)and then from that, you could just select the dt column and insert it
You should be using the trunc function to truncate the date to the first of the month. That eliminates the conversion of the date to a string and the need to convert the string back to a date.
select country, count(*) as members , trunc(joined, 'MM') from table group by country, trunc(joined, 'MM')