2日期间的所有年份和月份SQL

Question

我有一个小问题，我有一个表称为项目，看起来像这样：

--------------------------------------- 
ProjectId | StartDate | EndDate | 
--------------------------------------- 
    1  | 01/01/2015 | 31/12/2017| 

它是更多钞票来获取所有几个月和几年这样的日期之间：

-------------------- 
| Month | Year | 
-------------------- 
    1  | 2015 | 
    2  | 2015 | 
    3  | 2015 | 
    4  | 2015 | 
    5  | 2015 | 
    6  | 2015 | 
    7  | 2015 | 
    8  | 2015 | 
    9  | 2015 | 
    10 | 2015 | 
    11 | 2015 | 
    12 | 2015 | 
    1  | 2016 | 
    2  | 2016 | 
    3  | 2016 | 
    4  | 2016 | 
    .  |  . | 
    .  |  . | 
    .  |  . | 
    12 | 2017 | 

Answer 1

下面是使用PostgreSQL函数generate_series和extract的方法：

SELECT extract(month FROM date) AS month, extract(year FROM date) AS year 
FROM (
    SELECT generate_series('2015-01-01'::date, '2017-12-31'::date, '1 month'::interval) AS date 
) AS date_range 

• https://www.postgresql.org/docs/current/static/functions-srf.html
• https://www.postgresql.org/docs/current/static/functions-datetime.html

你你需要修改这个以使用你的日期表：

SELECT extract(month FROM range) AS month, extract(year FROM range) AS year 
FROM (
    SELECT generate_series(StartDate, EndDate, '1 month'::interval) AS range 
    FROM project 
    WHERE ProjectId = 1 
) AS date_range 

Answer 2

如果你的数据库是sql server，你可以运行下面的代码来得到结果。

DECLARE @DateStart DATETIME = '2015-01-01' 
DECLARE @DateEnd DATETIME = ' 2017-12-31'; 
WITH Dates AS 
(
    SELECT DATEADD(DAY, -(DAY(@DateStart) - 1), @DateStart) AS [Date] 
    UNION ALL 
    SELECT DATEADD(MONTH, 1, [Date]) 
    FROM Dates 
    WHERE [Date] < DATEADD(DAY, -(DAY(@DateEnd) - 1), @DateEnd) 
) 
SELECT 
MONTH([Date]) AS [Month], 
YEAR([Date]) AS [Year] 
FROM Dates; 

希望它能帮上忙。如果您需要更多的帮助，你可以看看下面的链接 https://blog.sqlauthority.com/2014/12/22/sql-server-list-the-name-of-the-months-between-date-ranges-part-2/

Answer 3

在SQL Server中，查询您的日期类型

;with datecte (date) 
AS 
(
    SELECT Convert(date,'01/01/2015',105) 
    UNION ALL 
    SELECT DATEADD(month,1,date) 
    from datecte 
    where DATEADD(month,1,date)<= (Select Convert(date,'31/12/2017',105)) 
) 
select month(date),YEAR(date) from datecte