SQL Server日期比较仅基于月份和年份 [英] SQL Server date comparisons based on month and year only

问题描述

我在确定仅基于月份和年份的SQL中比较日期的最佳方法时遇到了麻烦。

I am having trouble determining the best way to compare dates in SQL based on month and year only.

我们基于日期进行计算，因为结算发生在

We do calculations based on dates and since billing occurs on a monthly basis the date of the month has caused more hindrance.

例如

DECLARE @date1 DATETIME = CAST('6/15/2014' AS DATETIME),
        @date2 DATETIME = CAST('6/14/2014' AS DATETIME)

SELECT * FROM tableName WHERE @date1 <= @date2

上面的示例自@起不会返回任何行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.

类似地，以下情况由于相同的原因使我感到悲伤。

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

做这个。有建议吗？

推荐答案

要处理不等式，例如，我想将日期/时间转换为YYYYMM表示形式，如字符串或整数。对于此示例：

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:

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;

我将查询写为：

SELECT *
FROM tableName
WHERE year(@date2) * 100 + month(@date2) BETWEEN year(@date1) * 100 + month(@date1) AND
                                                 year(@date3) * 100 + month(@date1);

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