CODEWITHSUNDEEP
sqlmysqldate
Tarski
Tarski

Reputation: 5500

MySQL Get last date in each month from column of dates

An example is shown below; imagine each commar separated date is a row in the database

Input: - 2010-01-11, 2010-01-18, 2010-01-25, 2010-02-01, 2010-02-08, 2010-02-15, 2010-02-22, 2010-03-01 it should return

Ouput: 2010-01-25, 2010-02-22, 2010-03-01

The output is derived by getting the last date in the month, note for March there is only one date in the database so we use that value.

I would appreciate it if someone could post some pseudo-SQL about how to approach this problem. Note, I am using My-SQL so those date functions are available.

Cheers,

Upvotes: 4

Views: 5570

Answers (3)

Surendra
Surendra

Reputation: 237

CREATE FUNCTION [dbo].[GetLastDateOfMonthBetweenTwoDates]

(

    @startdate  DATETIME,
    @enddate    DATETIME

)

RETURNS TABLE
AS

    RETURN (
        with cte
        as
        (
            select cast(cast(year(@startdate) as varchar(4))+'-'+right(100+month(@startdate),2)+'-01' as datetime) ymd
            union all
            select dateadd(m,1,ymd)
            from cte
            where dateadd(m,1,ymd)<=dateadd(m,1,@enddate)
        )
        select dateadd(d,-1,ymd) ymd from cte where dateadd(d,-1,ymd) between @startdate and @enddate
    )

Upvotes: 0

vicatcu
vicatcu

Reputation: 5837

SELECT max(date_field) FROM date_table GROUP BY YEAR(date_field), MONTH(date_field) ORDER BY date_field ASC;

Upvotes: 2

BenM
BenM

Reputation: 4153

SELECT MAX(datecol) FROM sometable
GROUP BY YEAR(datecol), MONTH(datecol);

The group by clause is by year and month so it will handle dates that break over a year.

Upvotes: 9

Related Questions