Gopal Biswas Gopal Biswas - 29 days ago 8
SQL Question

Sql query to generate monthly pay dates within a date range

I want to generate monthly pay dates based on a given date and date range.Suppose I have selected "01/30/2016" as a starting pay date and date range is the year 2016.Then the pay dates will be (total days of month -1) th of other months throughout the year 2016. I have added a screenshot for the result set for the pay date "01/30/2016".
I want to know the query in SQL.

Any help will be appreciated.

enter image description here

Answer

You can as the below:

DECLARE @SelectedDate DATETIME = '2016.01.30'
SELECT
     DATEADD(DAY, -1 - (DAY(EOMONTH(@SelectedDate)) - DAY(@SelectedDate)),  DATEADD(MONTH, v.m, DATEFROMPARTS(YEAR(@SelectedDate), 1, 1)))   
FROM
    (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS v(m)

How many days are left before the end of the month. The number of days found is removed from other months.

DAY(EOMONTH(@SelectedDate)(31) - DAY(@SelectedDate)(30) = left days(1)

Result:

2016-01-30
2016-02-28
2016-03-30
2016-04-29
2016-05-30
2016-06-29
2016-07-30
2016-08-30
2016-09-29
2016-10-30
2016-11-29
2016-12-30