Сравнение дат SQL Server по месяцам и годам

У меня возникли проблемы с определением наилучшего способа сравнения дат в SQL на основе только месяца и года.

Мы делаем расчеты на основе дат и, поскольку выставление счетов происходит ежемесячно, дата месяца привела к большим помехам.

Например

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. Поэтому я хотел бы найти способ вывести день из уравнения.

Аналогично, следующая ситуация дает мне горе по той же причине.

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 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

Должен быть более простой способ сделать это. Любые предложения?

Ответы

Ответ 1

Чтобы обрабатывать неравенства, например, между ними, мне нравится преобразовывать дату/время в представление 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;

Я бы написал запрос как:

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

Ответ 2

Вы можете отфильтровать месяц и год заданной даты на текущую дату следующим образом:

SELECT * 
FROM tableName 
WHERE month(date2) = month(getdate()) and year(date2) = year(getdate())

Просто замените метод GETDATE() на нужную дату.

Ответ 3

Вы можете присоединиться к значениям MONTH и YEAR этих дат:

SELECT * 
FROM tableName 
WHERE YEAR(@date1) = YEAR(@date2) AND MONTH(@date1) = MONTH(@date2)

Ответ 4

Во-первых, я бы использовал недвусмысленный формат дат, например, стандартный 'YYYYMMDD' а не '6/15/2014' вы использовали. Блог Аарона Бертрана объясняет гораздо лучше, чем я, различные способы, которыми это может пойти не так:
Вредные привычки: неправильная обработка запросов даты/диапазона

Для конкретной проблемы ваш последний запрос, который находит первые и последние дни месяцев (для даты1 и даты3), по моему мнению, находится на правильном пути. Вам нужны первые дни месяцев (первый день даты 1 и первый день следующего месяца для даты 3), если вы избегаете зла BETWEEN: Что общего между МЕЖДУ и дьяволом?

SELECT * 
FROM tableName 
WHERE date2 >= DATEADD(month, DATEDIFF(month, '19000101', @date1), '19000101')
  AND date2 < DATEADD(month, 1+DATEDIFF(month, '19000101', @date3), '19000101') ;

Запрос работает как есть, независимо от типа данных date2 (DATE, DATETIME, DATETIME2 или SMALLDATTEIME).

Бонусный момент, индексы на date2 будут учитываться оптимизатором таким образом.

Улучшение, согласно (еще одному) сообщению в блоге Аарона, чтобы избежать проблемы с оценкой мощности при оценке выражений с помощью DATEDIFF():
Производственные сюрпризы и предположения: 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))) ;

Ответ 5

Вы можете использовать форматирование даты, например, "ггггММ", поэтому будет выбран только месяц в том же году.

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')