SQL how to group by year end-CodePudding

I am not well advanced in SQL. Maybe someone could help me with this little problem. I need to summarize data by year end. I know I can use

SELECT Year(Mydate) as year,
       Sum(Amount)  as amount
FROM table1
GROUP BY Year(Mydate)

Mydate here is full date column (i.e. 15/6/2020). However in this case I get year as year number. I need to get year as year end date, i.e. 12/31/2021. How can I do that?

CodePudding user response：

Try format your field using DATE_FORMAT: (DATE_FORMAT(Mydate, '%Y/12/31')

https://www.w3schools.com/sql/func_mysql_date_format.asp

CodePudding user response：

Try:

SELECT 
  Year(Mydate) as year, 
  Convert(datetime, Str(Year(Mydate))   ' Dec 31', 103) as eoyDec31a
  Convert(char(10), Convert(datetime, Str(Year(Mydate))   ' Dec 31', 103), 103) As eoyDec31b,
  Max(Mydate) As eoyMax,
  Sum(Amount)  as amount
FROM table1
GROUP BY 
  Year(Mydate), 
  Convert(datetime, Str(Year(Mydate))   ' Dec 31', 103),
  Convert(char(10), Convert(datetime, Str(Year(Mydate))   ' Dec 31', 103), 103) 
Order By 1

If Dec 31 occurs in the table for every year, just the eoyMax column will do. If not the end of year has to be created from the parts: 2012 dec 31 etc.

eoyDec31a is the american format and eoyDec31b is the date string in british format.

The Convert function converts from string to datetime and back.

CodePudding user response：

OK, this seems to solve my issue:

SELECT DATEFROMPARTS(Year(Mydate),12,31)  AS year,
       Sum(Amount)  as amount
FROM table1
GROUP BY DATEFROMPARTS(Year(Mydate),12,31)