How to find the date collection between two dates in SQL Server for mail delinquency?

user_7781490 0
Viewed 63

I have to get the date collection of every month between two dates. I have no more idea in detail for SQL Server.

Example 1:

If my StartDate is '01/20/2017' (MM/dd/yyyy) and EndDate is '12/20/2017' (MM/dd/yyyy) than the expected result should be as given below.

2017-01-20
2017-02-20
2017-03-20
2017-04-20
2017-05-20
2017-06-20
2017-07-20
2017-08-20
2017-09-20
2017-10-20
2017-11-20
2017-12-20

Example 2:

If my StartDate is '01/30/2017' (MM/dd/yyyy) and EndDate is '12/20/2017' (MM/dd/yyyy) than the expected result should be as given below.

In this example StartDate is '01/30/2017' therefor in February month there is no 30th date in calandar so I need last date of this month. If any leap year will come in the range of these given dates than 29th date will be in result set.

Expected result

2017-01-30
2017-02-28
2017-03-30
2017-04-30
2017-05-30
2017-06-30
2017-07-30
2017-08-30
2017-09-30
2017-10-30
2017-11-30

Thanks in advance.

sql-server sql
1 Answers

Please try the below solution. Just set the MinDate and MaxDate as per your requirement. Also please check for the February month date in case of MinDate will be 30 or 31.

DECLARE @MinDate datetime,
        @MaxDate datetime

SELECT  @MinDate = '01/30/2017',
        @MaxDate = '12/17/2020'


DECLARE @FilteredDate DATETIME = GETDATE(); 

;WITH CTE AS
(
 SELECT  TOP (DATEDIFF(MONTH, @MinDate, @MaxDate) + 1) DATEADD(MONTH, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate) AS CurrentDate
 FROM    sys.all_objects a
 CROSS JOIN sys.all_objects b
)
SELECT * FROM CTE
WHERE CurrentDate > CONVERT(DATE, @FilteredDate) AND
CurrentDate <= CONVERT(DATE, @MaxDate)
ORDER BY CurrentDate ASC
user_30960590
Related