Converting Date Columns into YYYY-MM format and then grouping all the data by date (MYSQL)-CodePudding

Table invoice_details having columns:

1.Customer_Number, 
2.Customer_Name,
3.Invoice_Date,
4.Due_Date,
5.Payment_Date,
6.Clearing_Date,
7. Invoice_Amount,
8.Open_Amount ,
9.Country,
10.Company_Code

I want to convert all date columns format to ‘YYYY-MM’ and exclude those customers who are living in ‘UK’ and finally grouping them by DATE in MYSQL.

CodePudding user response：

SELECT 
    DATE_FORMAT(Invoice_Date, "%Y-%m") AS Invoice_Date_formatted,
    DATE_FORMAT(Due_Date, "%Y-%m") AS Due_Date_formatted,
    DATE_FORMAT(Payment_Date, "%Y-%m") AS Payment_Date_formatted,
    DATE_FORMAT(Clearing_Date, "%Y-%m") AS Clearing_Date_formatted
FROM 
    invoice_details
WHERE 
    Country = 'UK'
GROUP BY 
    DATE(<date-field-that-you-need-the-query-to-be-grouped>)