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.
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments