Получить абсолютную разницу дат
У меня есть простой SQL-запрос, показанный ниже. Если вы посмотрите на сценарий 1, разница между датами составляет 1 месяц и это правильно (потому что 5 октября — 5 сентября).
Однако, когда вы смотрите на сценарий 2, разница между датами по-прежнему отображается как 1. Но она должна быть 0 (потому что с 4 октября по 5 сентября все еще не 1 месяц).
Можем ли мы убедиться, что сценарий 2 отображается как 0?
Сценарий 1
select
datediff(month, convert(datetime, '05-09-24', 5), convert(datetime, '05-10-24', 5))
1
Сценарий 2
select
datediff(month, convert(datetime, '05-09-24', 5), convert(datetime, '04-10-24', 5))
1
Не так. Здесь DATEDIFF извлекает год и месяц из двух дат и вычисляет разницу. Вычислить дельту месяцев так, как вы это описываете, сложно, потому что продолжительность месяца варьируется. Вы можете извлечь месяц и день отдельно и сравнить их.
DATEDIFF вычисляет количество пересекаемых границ для данного типа. Таким образом, со 2 сентября по 31 августа — это два дня, но все равно это будет один полный месяц, поскольку он пересекает границу. Кроме того, по историческим причинам ваши литералы даты для значений только даты (без компонента времени) при написании t-sql должны использовать формат yyyyMMdd (без дефисов). Тогда вы также можете смело использовать cast() вместо convert().
«Но оно должно быть 0 (потому что 4 октября — 5 сентября — это еще не 1 месяц)» Ну, вы указываете параметр как MONTH. Октябрь-сентябрь = 1, так что это правильно.
Возможно, это немного больше, чем вы ищете, но это можно сократить stackoverflow.com/questions/43126223/…
Ответы 4
Это та вещь, которая вам нужна.
SELECT DATEDIFF(month,
CONVERT(DATE, '05/09/24', 5),
CONVERT(DATE, '04/10/24', 5)
) - IIF(
DAY(CONVERT(DATE, '05/09/24', 5)) >
DAY(CONVERT(DATE, '04/10/24', 5)),
1,0
);
Это дает 0.
Я бы использовал выражение регистра и вычитал бы 1, если дата в секундах имеет меньший день.
DECLARE @date1 DateTime
DECLARE @date2 DateTime
SET @date1=convert(datetime,'05-09-24',5); --Italian 5 = dd-mm-yy
SET @date2=convert(datetime,'04-10-24',5);
SELECT CASE WHEN DatePart(day,@date1) < DatePart(day,@date2) THEN
THEN DATEDIFF(month, @date1, @date2)
ELSE DATEDIFF(month, @date1, @date2) -1
END as AbsoltuteDiff
Я считаю, что < должно быть <= в приведенном выше примере. Один и тот же начальный/конечный день месяца не требует корректировки. (У вас также есть дополнительный THEN.)
DATEDIFF() вычисляет количество пересекаемых границ для данного сегмента. Таким образом, период со 2 сентября по 31 августа составляет два дня (дважды пересекает границы дней), но все равно всегда будет одним полным месяцем, поскольку он пересекает границу.
Эта функция возвращает количество (в виде целочисленного значения со знаком) указанных границ частей даты, пересекаемых между указанной начальной датой и конечной датой.
Невозможно заставить его показывать 0 вместо 1, если значения действительно пересекают границу, а использование меньших сегментов, таких как day, может сбить вас с толку в нечетное время. Единственный вариант — также проверить значение дня обоих входных данных, чтобы решить, хотите ли вы также вычесть 1 из конечного результата.
Если вас интересуют только даты (а не время), вы можете использовать выражение:
DATEDIFF(month, date1, date2)
- CASE WHEN DAY(date1) > DAY(date2) THEN 1 ELSE 0 END
Если вы заботитесь о времени и считаете, что разница между 2024-07-15 16:44:30 и 2024-08-15 16:44:29 меньше одного месяца (на одну секунду), вы можете использовать следующее:
DATEDIFF(month, datetime1, datetime2)
- CASE WHEN DAY(datetime1) > DAY(datetime2)
OR (
DAY(datetime1) = DAY(datetime2)
AND CAST(datetime1 AS TIME) > CAST(datetime2 AS TIME
)
THEN 1 ELSE 0 END
или поочередно:
DATEDIFF(month, datetime1, datetime2)
- CASE WHEN DATEADD(month, DATEDIFF(month, datetime1, datetime2), datetime1)
> datetime2
THEN 1 ELSE 0 END
Каждый из них будет уменьшать разницу месяцев на 1, если первая дата/время в месяце позже второй даты/времени.
Обратите внимание, что для даты/времени начала, приближающихся к концу месяца, когда конечные месяцы короче, месяц не будет засчитан до тех пор, пока дата не перейдет на 1-е число следующего месяца. Например:
- От
2024-01-31 16:45до2024-02-29 23:59считается 0 месяцев. - С
2024-01-31 16:45по2024-03-01 00:00считается 1 месяц.
Примеры результатов:
Raw Разница
1 Разница
2 2024-09-05 00:00 2024-10-05 00:00 1 месяц 1 1 1 2024-09-05 00:00 2024-10-04 00:00 1 день меньше 1 месяца = 0 месяцев 1 0 0 2024-09-05 00:00 2024-12-04 00:00 1 день меньше 3 месяцев = 2 месяца 3 2 2 2024-09-05 00:00 2024-12-05 00:00 3 месяца 3 3 3 2024-09-05 00:00 2024-12-31 00:00 3 месяца, 26 дней 3 3 3 2024-09-05 00:00 2025-01-01 00:00 3 месяца, 27 дней 4 3 3 2023-12-31 23:59 2024-01-01 00:00 1 минута, пересечение границы месяца 1 0 0 2024-07-15 16:45 2024-08-15 16:44 1 минута меньше 1 месяца = 0 месяцев 1 1 0 2024-07-15 16:45 2024-08-15 16:45 ровно 1 месяц 1 1 1 2024-01-31 16:45 2024-02-29 23:59 Еще нет 1 месяца в конце короткого месяца 1 0 0 2024-01-31 16:45 2024-03-01 00:00 1 месяц после перехода короткого месяца на следующий 2 1 1 2024-02-29 16:45 2024-03-30 00:00 1 месяц от короткого до длинного 1 1 1
Где:
DiffRaw— оригинальныйDATEDIFF()расчет.Diff1— расчет корректировки только за день сверху.Diff2— это расчет дня + времени сверху.
См. эту db<>fiddle для демонстрации.
Добавлено несколько тестовых случаев (все проходят): dbfiddle.uk/rlfHMWnH
Второе значение правильное;
DATEDIFFсообщает вам, сколько тиков произошло, а не прошло ли так много.DATEDIFF(YEAR,'20231232 23:59:59','20240101 00:00:00')есть1, потому что, хотя прошла всего 1 секунда, они в разные годы.