SQL query to get list of month between two dates

Question

I need a custom output of dates between two dates.Let me explain What exactly I need.

Lets say I have two dates '2016-01-01' and '2016-12-31' yyyy-mm-dd Now I want to display list of months/date between these two dates but in order of 3 months,6,9,12,18,24 etc

E.g.

E.g. If I enter start and end date as '2016-01-01' and '2016-12-31' then output should display till 12 months as difference between start and end is 12.

So output should be

3 Months    1/1/2016    4/1/2016
6 Months    1/1/2016    7/1/2016
9 Months    1/1/2016    10/1/2016
12 Months   1/1/2016    1/1/2017

If start and end date are '2016-01-01' and '2016-02-15' then output should display 3 months as end date comes within 3

So output should be

3 Months    1/1/2016    4/1/2016

sql is as below

declare @startdate date = '2016-01-01', @endDate DATE = '2016-12-31'

declare @mindate date, @maxdate date
    SET @mindate = @startdate;
    SET @maxdate = @endDate



        INSERT INTO @ReportMonths(ReportMonth,MinMOnth,MaxMonth) values
    ( '3 Months',@mindate,dateadd(month,3,@mindate)),
    ( '6 Months',@mindate,dateadd(month,6,@mindate)),
    ( '9 Months',@mindate,dateadd(month,9,@mindate)),
    ( '12 Months',@mindate,dateadd(month,12,@mindate)),
    ( '18 Months',@mindate,dateadd(month,18,@mindate)),
    ( '24 Months',@mindate,dateadd(month,24,@mindate)),
    ( '36 Months',@mindate,dateadd(month,36,@mindate)),
    ( '48 Months',@mindate,dateadd(month,48,@mindate)),
    ( '60 Months',@mindate,dateadd(month,60,@mindate)),
    ( '72 Months',@mindate,dateadd(month,72,@mindate))

issue is how do i delete rest of rows when end date doesn't comes in Maxdate column

Answer 1

You might try this

declare @mindate date, @maxdate date
    SET @mindate = {d'2016-01-01'};
    SET @maxdate = {d'2016-02-15'};

DECLARE @ReportMonths TABLE(ReportMonth VARCHAR(100),MinMOnth DATE,MaxMonth DATE)
INSERT INTO @ReportMonths VALUES
    ( '3 Months',@mindate,dateadd(month,3,@mindate)),
    ( '6 Months',@mindate,dateadd(month,6,@mindate)),
    ( '9 Months',@mindate,dateadd(month,9,@mindate)),
    ( '12 Months',@mindate,dateadd(month,12,@mindate)),
    ( '18 Months',@mindate,dateadd(month,18,@mindate)),
    ( '24 Months',@mindate,dateadd(month,24,@mindate)),
    ( '36 Months',@mindate,dateadd(month,36,@mindate)),
    ( '48 Months',@mindate,dateadd(month,48,@mindate)),
    ( '60 Months',@mindate,dateadd(month,60,@mindate)),
    ( '72 Months',@mindate,dateadd(month,72,@mindate));

--The query will return all rows of @ReportMonths, with the following MaxMonth included. This is the smallest of the bigger

SELECT rm.*
FROM @ReportMonths AS rm
WHERE rm.MaxMonth<=(
                        SELECT MIN( MaxMonth)
                        FROM @ReportMonths 
                        WHERE MaxMonth > @maxdate
                    );

Answer 2

Try This
 Select * FROM TABLE_NAME WHERE dates BETWEEN '2016-01-01' and '2016-12-31'
     or
 Select * FROM TABLE_NAME WHERE dates BETWEEN '20160101' and '20161231'

Answer 3

If i understood the problem correctly, you can do as follows. I find the difference of months between dates. Then I use it in the where clause.

SELECT *
FROM
    @ReportMonths
WHERE
    LEFT(ReportMonth, LEN(ReportMonth) - 7) * 1 <= ((DATEDIFF(MONTH, @mindate, @maxdate) / 3) + 1) * 3

Result for:

DECLARE @mindate DATE = '2016.01.01' -- yyyy.mm.dd
DECLARE @maxdate DATE = '2016.12.31' -- yyyy.mm.dd

ReportMonth     MinMOnth   MaxMonth
--------------- ---------- ----------
3 Months        2016-01-01 2016-04-01
6 Months        2016-01-01 2016-07-01
9 Months        2016-01-01 2016-10-01
12 Months       2016-01-01 2017-01-01