在 SQL Server 中获取给定月份和年份的所有日期 [英] Get All Dates of Given Month and Year in SQL Server

问题描述

我想通过在 SQL Server 中声明月份和年份来获取所有日期.

I want to get all dates by declaring month and year in SQL server.

任何人都可以分享简单的 SQL 代码行以获取它.

Can anyone please share easy lines of SQL code to get it.

例如:

DECLARE @month AS INT = 5
DECLARE @Year AS INT = 2016
SELECT * from Something

我尝试了以下内容，

DECLARE @month TINYINT=5

;WITH CTE_Days AS (
    SELECT DATEADD(
               MONTH,
               @month,
               DATEADD(
                   MONTH,
                   -MONTH(GETDATE()),
                   DATEADD(
                       DAY,
                       -DAY(GETDATE()) + 1,
                       CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
                   )
               )
           ) Dates
    UNION ALL
    SELECT DATEADD(DAY, 1, Dates)
    FROM   CTE_Days
    WHERE  Dates < DATEADD(
               DAY,
               -1,
               DATEADD(
                   MONTH,
                   1,
                   DATEADD(
                       MONTH,
                       @month,
                       DATEADD(
                           MONTH,
                           -MONTH(GETDATE()),
                           DATEADD(
                               DAY,
                               -DAY(GETDATE()) + 1,
                               CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
                           )
                       )
                   )
               )
           )
)
SELECT Dates
FROM   CTE_Days

但我正在寻找行数少且答案简短的简单解决方案

But I am looking for easy solution with less lines and short answer

推荐答案

你不能仅仅通过声明月份来获得所有的日子，你需要加上年份，因为闰年​​:

You can't get all days just by declaring the month, you need to add the year as well because of leap years:

DECLARE @date DATE = getdate()

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)
SELECT top(day(EOMONTH(@date)))
  N day,
  dateadd(d,N-1, @date) date
FROM tally

另一种不同的解决方案(由 t@clausen 提供):

Another different solution(by t@clausen):

DECLARE @month AS INT = 5
DECLARE @Year AS INT = 2016

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)
SELECT N day,datefromparts(@year,@month,N) date FROM tally
WHERE N <= day(EOMONTH(datefromparts(@year,@month,1)))

这篇关于在 SQL Server 中获取给定月份和年份的所有日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！