仅基于月和年的 SQL Server 日期比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26239784/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server date comparisons based on month and year only
提问by Andy Evans
I am having trouble determining the best way to compare dates in SQL based on month and year only.
我无法确定仅基于月和年比较 SQL 中日期的最佳方法。
We do calculations based on dates and since billing occurs on a monthly basis the date of the month has caused more hindrance.
我们根据日期进行计算，并且由于按月计费，因此每月的日期造成了更多障碍。
For example
例如
DECLARE @date1 DATETIME = CAST('6/15/2014' AS DATETIME),
@date2 DATETIME = CAST('6/14/2014' AS DATETIME)
SELECT * FROM tableName WHERE @date1 <= @date2
The above example would not return any rows since @date1 is greater than @date2. So I would like to find a way to take the day out of the equation.
上面的示例不会返回任何行，因为 @date1 大于 @date2。所以我想找到一种方法来摆脱等式。
Similarly, the following situation gives me grief for same reason.
同样，以下情况也同样让我感到悲伤。
DECLARE @date1 DATETIME = CAST('6/14/2014' AS DATETIME),
@date2 DATETIME = CAST('6/15/2014' AS DATETIME),
@date3 DATETIME = CAST('7/1/2014' AS DATETIME)
SELECT * FROM tableName WHERE @date2 BETWEEN @date1 AND @date3
I've done inline conversions of the dates to derive the first day and last day of the month for the date specified.
我已经完成了日期的内联转换，以得出指定日期的月份的第一天和最后一天。
SELECT *
FROM tableName
WHERE date2 BETWEEN
DATEADD(month, DATEDIFF(month, 0, date1), 0) -- The first day of the month for date1
AND
DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, date2) + 1, 0)) -- The lastday of the month for date3
There has to be an easier way to do this. Any suggestions?
必须有一种更简单的方法来做到这一点。有什么建议？
回答by Gordon Linoff
To handle inequalities, such as between, I like to convert date/times to a YYYYMM representation, either as a string or an integer. For this example:
为了处理不等式，例如在两者之间，我喜欢将日期/时间转换为 YYYYMM 表示，无论是作为字符串还是整数。对于这个例子：
DECLARE @date1 DATETIME = CAST('6/14/2014' AS DATETIME),
@date2 DATETIME = CAST('6/15/2014' AS DATETIME),
@date3 DATETIME = CAST('7/1/2014' AS DATETIME);
SELECT * FROM tableName WHERE @date2 BETWEEN @date1 AND @date3;
I would write the query as:
我会将查询写为：
SELECT *
FROM tableName
WHERE year(@date2) * 100 + month(@date2) BETWEEN year(@date1) * 100 + month(@date1) AND
year(@date3) * 100 + month(@date1);
回答by Tanner
You can filter the month and year of a given date to the current date like so:
您可以将给定日期的月份和年份过滤为当前日期，如下所示：
SELECT *
FROM tableName
WHERE month(date2) = month(getdate()) and year(date2) = year(getdate())
Just replace the GETDATE()method with your desired date.
只需将GETDATE()方法替换为您想要的日期。
回答by DavidG
回答by ypercube??
First, I'd use a format for the dates that is unambiguous, like the standard 'YYYYMMDD'and not the '6/15/2014'you have been using. Aaron Bertrand's blog explains far better than I could, the various ways this can go wrong:
Bad habits to kick : mis-handling date / range queries
首先，我会使用明确的日期格式，例如标准'YYYYMMDD'而不是'6/15/2014'您一直使用的格式。Aaron Bertrand 的博客比我能解释的要好得多，这可能会出错的各种方式：
要踢的坏习惯：错误处理日期/范围查询
For the specific problem, your last query which finds the first and the last days of the months (for date1 and date3), is in my opinion on the right track. You only need though the first days of months (first day of date1 and first day of next month for date3), if you avoid the evil BETWEEN: What do BETWEEN and the devil have in common?
对于特定问题，您的最后一个查询找到月份的第一天和最后一天（对于 date1 和 date3），在我看来是正确的。如果你避免邪恶BETWEEN，你只需要几个月的第一天（date1 的第一天和 date3 的下个月的第一天），如果你避免邪恶：之间有 什么共同点？
SELECT *
FROM tableName
WHERE date2 >= DATEADD(month, DATEDIFF(month, '19000101', @date1), '19000101')
AND date2 < DATEADD(month, 1+DATEDIFF(month, '19000101', @date3), '19000101') ;
The query works as it is, no matter the datatype of date2(DATE, DATETIME, DATETIME2or SMALLDATTEIME).
该查询工作，因为它是，无论的数据类型date2（DATE，DATETIME，DATETIME2或SMALLDATTEIME）。
Bonus point, indexes on date2will be considered by the optimizer this way.
加分点，date2优化器会以这种方式考虑索引。
Improvement, according to (yet, another) Aaron's blog post, to avoid a problem with cardinality estimation when evaluating expressions with DATEDIFF():
Performance Surprises and Assumptions : DATEDIFF
根据（又一个）Aaron 的博客文章进行改进，以避免在评估表达式时出现基数估计问题DATEDIFF()：
Performance Surprises and Assumptions : DATEDIFF
SELECT *
FROM tableName
WHERE date2 >= CONVERT(DATE, DATEADD(day, 1 - DAY(@date1), @date1))
AND date2 < DATEADD(month, 1,
CONVERT(DATE, DATEADD(day, 1 - DAY(@date3), @date3))) ;
回答by Lee-UK
I rolled my dates up to the same date using EOMONTH() and compared them. So, if I had date1 as 09/18/2019 and date2 as 09/16/2019 they would both roll up to 09/30/2019. It isn't a direct answer to your question, but it worked in my situation.
我使用 EOMONTH() 将日期滚动到同一日期并进行比较。因此，如果我将 date1 设为 09/18/2019，将 date2 设为 09/16/2019，它们都会累积到 09/30/2019。这不是对您问题的直接回答，但它适用于我的情况。
回答by Andy Ferdian
You can use formatting your date like this 'yyyyMM' , so only the month in the same year will be selected.
您可以像这样使用 'yyyyMM' 格式化日期，因此只会选择同年的月份。
SELECT *
FROM tableName
WHERE FORMAT(date_month_bill, 'yyyyMM') < FORMAT(DATEADD(MONTH, -1, GETDATE()), 'yyyyMM')
AND FORMAT(date_month_bill, 'yyyyMM') > FORMAT(DATEADD(MONTH, -3, GETDATE()), 'yyyyMM')
