SQL查询以基于月结束日期生成月薪日期

与您的最后一个问题相同的逻辑。

您可以如下所示：

DECLARE @StartDate DATETIME = '2016.01.26'
DECLARE @EndDate DATETIME = '2016.01.31'

SELECT
    DATENAME(dw, A.Month) DayNameOfMonth,
    A.[Month],
    DATENAME(dw, A.[Pay Date]) DayNameOfPayDate,
    A.[Pay Date]
FROM
(
    SELECT
         DATEADD(DAY, -1 - (DAY(EOMONTH(@EndDate)) - DAY(@EndDate)),  DATEADD(MONTH, v.m, DATEFROMPARTS(YEAR(@EndDate), 1, 1))) 'Month',
         DATEADD(DAY, -1 - (DAY(EOMONTH(@StartDate)) - DAY(@StartDate)),  DATEADD(MONTH, v.m, DATEFROMPARTS(YEAR(@StartDate), 1, 1))) 'Pay Date'
    FROM
        (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS v(m)
) A

结果：

DayNameOfMonth                 Month      DayNameOfPayDate               Pay Date
------------------------------ ---------- ------------------------------ ----------
Sunday                         2016-01-31 Tuesday                        2016-01-26
Monday                         2016-02-29 Wednesday                      2016-02-24
Thursday                       2016-03-31 Saturday                       2016-03-26
Saturday                       2016-04-30 Monday                         2016-04-25
Tuesday                        2016-05-31 Thursday                       2016-05-26
Thursday                       2016-06-30 Saturday                       2016-06-25
Sunday                         2016-07-31 Tuesday                        2016-07-26
Wednesday                      2016-08-31 Friday                         2016-08-26
Friday                         2016-09-30 Sunday                         2016-09-25
Monday                         2016-10-31 Wednesday                      2016-10-26
Wednesday                      2016-11-30 Friday                         2016-11-25
Saturday                       2016-12-31 Monday                         2016-12-26

更新

DECLARE @StartDate DATETIME = '2016.01.31' -- month end
DECLARE @EndDate DATETIME = '2016.02.08' -- pay date

SELECT         
    DATEADD(DAY, -1 - (DAY(EOMONTH(@StartDate)) - DAY(@StartDate)),  DATEADD(MONTH, v.m, DATEFROMPARTS(YEAR(@StartDate), 1, 1))) 'Month End',        
    DATEADD(DAY, DATEDIFF(DAY, @StartDate, @EndDate), DATEADD(DAY, -1 - (DAY(EOMONTH(@StartDate)) - DAY(@StartDate)),  DATEADD(MONTH, v.m, DATEFROMPARTS(YEAR(@StartDate), 1, 1)))) 'Pay Date'
FROM
    (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS v(m)

更新2

DECLARE @StartDate DATETIME = '2016.01.31' -- month end
DECLARE @EndDate DATETIME = '2016.02.08' -- pay date

SELECT
    *
FROM
(
    SELECT         
        DATEADD(DAY, -1 - (DAY(EOMONTH(@StartDate)) - DAY(@StartDate)),  DATEADD(MONTH, v.m, DATEFROMPARTS(YEAR(@StartDate), 1, 1))) 'Month End',        
        DATEADD(DAY, DATEDIFF(DAY, @StartDate, @EndDate), DATEADD(DAY, -1 - (DAY(EOMONTH(@StartDate)) - DAY(@StartDate)),  DATEADD(MONTH, v.m, DATEFROMPARTS(YEAR(@StartDate), 1, 1)))) 'Pay Date'
    FROM
        (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS v(m)
) A
WHERE
    YEAR(A.[Pay Date]) = YEAR(@EndDate)