Sql查询生成日期范围内的每月付款日期 [英] Sql query to generate monthly pay dates within a date range

问题描述

我想根据给定的日期和日期范围生成每月付款日期。我选择01/30/2016作为起始付款日期，日期范围是2016年。然后付款日期将（我在2016年的其他月份的总天数-1）。我添加了截止日期为01/30/2016的结果集。
我想知道SQL中的查询。

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.

任何帮助将不胜感激。

a href =https://i.stack.imgur.com/UCoh9.jpg =nofollow noreferrer>

推荐答案

您可以如下所示：

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）

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

结果：

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

这篇关于Sql查询生成日期范围内的每月付款日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！