sql server中两个日期之间的月份，sql server中每个日期的开始和结束日期 [英] months between two dates in sql server with starting and end date of each of them in sql server

问题描述

我想获得两个日期之间的月份，分别是开始日期和结束日期。假设我将startdate输入为 2017-04-01，将enddate输入为 2017-07-31，我希望输入月份列表，即April，五月，六月，七月分别带有开始日期和结束日期。请给我建议如何实现。

i want to get months between two dates with their starting and end dates.Suppose if i enter startdate as "2017-04-01" and enddate as "2017-07-31", i want list of months i.e April,May,June,July with their starting and end date respectively.Kindly suggest me how it can be achieved.

推荐答案

一种方法是递归CTE：

One method is a recursive CTE:

with cte as (
      select dateadd(day, 1 - day(@startdate), @startdate) as som,
             eomonth(@startdate) as eom
      union all
      select dateadd(month, 1, som), eomonth(dateadd(month, 1, som))
      from cte
      where dateadd(month, 1, som) < @enddate
     )
select *
from cte;

如果要输入月份名称，则可以使用 datename（个月）。。

If you want the name of the month, then you can use datename(month, som).

这篇关于sql server中两个日期之间的月份，sql server中每个日期的开始和结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！