Question 20634 in Mysql Question posted in
The official MySQL documentation can be found here.

Trying to get a SQL query to retrieve data by month instead of by day – Mysql

I have a SQL query:

Select * from random_table where dt >= date('12/1/2022')

The problem with this is its giving me daily data. Can I adjust this code and add date_trunc to aggregate by month, if so, how would i do that?

I haven’t tried anything yet as I am a noob when it comes to SQL

Tags:

2

Answers


  1. 0

    Maybe you can use DATE_FORMAT, and select your aggregation function

    SELECT DATE_FORMAT(dt, '%Y-%m'), SUM(...)
FROM random_table
where dt >= date('12/1/2022')
GROUP BY 1;
    Login or Signup to reply.
  2. 0

    Using STR_TO_DATE(dt, '%m/%d/%Y') to convert your string to date, and DATE_FORMAT to compare with the month needed

    select *
from random_table
where DATE_FORMAT(STR_TO_DATE(dt, '%m/%d/%Y'), '%Y-%m') = '2022-12';

    To show month instead of day :

    Select id, name, DATE_FORMAT(STR_TO_DATE(dt, '%m/%d/%Y'), '%Y-%m') as month
from random_table
where DATE_FORMAT(STR_TO_DATE(dt, '%m/%d/%Y'), '%Y-%m') = '2022-12';

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search