sql server语言 - 在sql中使用月和年数字查询日期范围

Question

DECLARE @startMonth int
DECLARE @endMonth int
DECLARE @startYear int
DECLARE @endYear int
SET @startMonth = 1
SET @endMonth =5
SET @startYear =2014
SET @endYear =2015
SELECT * FROM Table 
         WHERE (YEAR(Date)>=@startYear AND MONTH(Date) >= @startMonth)
              AND (YEAR(Date)<=@endYear AND MONTH(Date) <= @endMonth)

这显然是返回从2014-01-01到2014-05-31之间的任何日期的结果

和2014-01-01至2015-05-31

但是我想要从2014-01-01到2015-05-31之间的任何日期。

如何更改查询?我应该这样写吗?

SELECT * FROM Table 
         WHERE Date>=DATEFROMPARTS ( @startYear, @startMonth, 1 ) 
           AND Date <= DATEFROMPARTS ( @endYear, @endMonth, 31 ))

然后我结束的问题是，如果@endMonth不包含31天。然后我必须创建另一个检查来确保结束日期的正确数字。

我相信一定有更好的方式来写这个。谢谢你的帮助。

Answer 1

SELECT * 
FROM your_table 
WHERE Date >= DATEFROMPARTS(@startYear, @startMonth, 1)
  AND Date <  DATEFROMPARTS(@endYear, @endMonth + 1, 1)

Answer 2

SELECT *
FROM
    <table_name>
WHERE
    date BETWEEN DATEFROMPARTS(@startYear,@startMonth,1) AND EOMONTH(DATEFROMPARTS(@endYear,@endMonth,1));