Дробный DATEDIFF для SQLServer?

Question

Недавно обнаружил что-то (и, конечно, моя вина за то, что я не читал документы), что довольно сильно напугало меня по поводу функции DATEDIFF в SQLServer; он подсчитывает количество интервалов между двумя указанными датами.

Это означает, что я могу спросить его о количестве дней между 01 Jan 23:57 а также 01 Jan 23:59 и он вернет 0, но если я попрошу дни между 01 Jan 23:59 а также 02 Jan 00:01 это скажет мне, что между ними есть 1 день. Временной интервал такой же: 2 минуты, но внезапно одна разница составляет 0 дней, а другая - 1 день

Исходя из опыта работы с Oracle и.Net, я вижу, что допустил грубую ошибку, предполагая, что DATEDIFF в TSQL работает одинаково (то есть готовит временной интервал, а затем округляет его до указанного интервала), но какова альтернатива?

Если я хочу точно определить, с десятичными знаками, сколько лет проходит между двумя датами, как мне это сделать в SQLServer? Я не хочу, чтобы результат с 1 января по 31 декабря, возвращающий 0 лет, но с 31 декабря 2000 года по 01 января 2002 года, возвращающий 2 года, потому что это грубые ошибки и мили от 0,997 и 1,005 (не точные расчеты), они должны больше скорее всего, будет..

Ответ явно не в DATEDIFF днях и делении на 365.0, не только потому, что datediff обычно "неправильный" даже для ДНЕЙ (согласно моему 2-минутному примеру), но также и потому, что не всегда 365 дней в году. То же самое для месяцев -> они не всегда имеют определенное количество интервалов, поэтому не имеет смысла брать дни и делить их на 31 (или 30, 29 или 28). По тем же причинам я не могу сделать простой (endDateTime - startDateTime)/x математический

Answer 1

ОБНОВЛЕНИЕ: этот код не включает DST.

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

DECLARE @dateFrom DATETIME, @dateTo DATETIME

SET @dateFrom = '2000-12-31 23:59'
SET @dateTo = '2002-01-02 00:01'


SELECT DATEDIFF(YEAR, DATEADD(year,DATEDIFF(year,0,DATEADD(year,1,@dateFrom)),0), DATEADD(year,DATEDIFF(year,0,@dateTo),0))
       + CASE WHEN YEAR(@dateFrom) < YEAR(@dateTo) THEN (DATEDIFF(MINUTE, @dateFrom, CAST(YEAR(@dateFrom) AS CHAR(4))+'-12-31 23:59') + 1) / (1.0*DATEDIFF(minute, CAST(YEAR(@dateFrom) AS CHAR(4))+'-01-01 00:00', CAST(YEAR(@dateFrom) AS CHAR(4))+'-12-31 23:59') + 1)
       + (DATEDIFF(MINUTE, CAST(YEAR(@dateTo) AS CHAR(4))+'-01-01 00:00',@dateTo ) + 1) / (1.0*DATEDIFF(minute, CAST(YEAR(@dateTo) AS CHAR(4))+'-01-01 00:00', CAST(YEAR(@dateTo) AS CHAR(4))+'-12-31 23:59') + 1)
       ELSE (DATEDIFF(MINUTE,@dateFrom, @dateTo) + 1) / (1.0*DATEDIFF(minute, CAST(YEAR(@dateTo) AS CHAR(4))+'-01-01 00:00', CAST(YEAR(@dateTo) AS CHAR(4))+'-12-31 23:59') + 1) END
--Result: 1.002745428591627

Этот код нуждается в комментариях, поэтому:

+1 необходимо получить точное количество минут между датами.

Это используется для подсчета количества минут в году.

(1.0*DATEDIFF(minute, CAST(YEAR(@dateTo) AS CHAR(4))+'-01-01 00:00', CAST(YEAR(@dateTo) AS CHAR(4))+'-12-31 23:59') + 1)

Это даст нам начало года.

DATEADD(year,DATEDIFF(year,0,@dateTo),0)

Этот кусок кода рассчитывает полные годы между @dateFrom а также @dateTo,

DATEDIFF(YEAR, DATEADD(year,DATEDIFF(year,0,DATEADD(year,1,@dateFrom)),0), DATEADD(year,DATEDIFF(year,0,@dateTo),0))

Это подсчет "частичных" лет. Мы подсчитываем, сколько минут осталось до конца года.

(DATEDIFF(MINUTE, @dateFrom, CAST(YEAR(@dateFrom) AS CHAR(4))+'-12-31 23:59') + 1) / (1.0*DATEDIFF(minute, CAST(YEAR(@dateFrom) AS CHAR(4))+'-01-01 00:00', CAST(YEAR(@dateFrom) AS CHAR(4))+'-12-31 23:59') + 1)

Подобно тому, что мы имеем выше, но для @dateTo,

(DATEDIFF(MINUTE, CAST(YEAR(@dateTo) AS CHAR(4))+'-01-01 00:00',@dateTo ) + 1) / (1.0*DATEDIFF(minute, CAST(YEAR(@dateTo) AS CHAR(4))+'-01-01 00:00', CAST(YEAR(@dateTo) AS CHAR(4))+'-12-31 23:59') + 1)

Здесь мы рассчитываем годы, когда @dateFrom а также @dateTo есть часть того же года.

(DATEDIFF(MINUTE,@dateFrom, @dateTo) + 1) / (1.0*DATEDIFF(minute, CAST(YEAR(@dateTo) AS CHAR(4))+'-01-01 00:00', CAST(YEAR(@dateTo) AS CHAR(4))+'-12-31 23:59') + 1)

Answer 2

Если вам нужна точность, вы можете потратить несколько минут на datediff и умножьте результат соответственно. Вы можете настроить @dateFrom а также @dateTo для проверки выходов с кодом ниже:

DECLARE @dateFrom DATETIME, @dateTo DATETIME

SET @dateFrom = '2017-07-01 23:59'
SET @dateTo = '2017-07-02 00:01'

SELECT DATEDIFF(MINUTE, @dateFrom, @dateTo) minsDiff

SELECT DATEDIFF(MINUTE, @dateFrom, @dateTo) / 60.0 hoursDiff

SELECT DATEDIFF(MINUTE, @dateFrom, @dateTo) / 60.0 / 24.0 daysDiff

SELECT DATEDIFF(MINUTE, @dateFrom, @dateTo) / 60.0 / 24.0 / 365.25 yearsDiff

SELECT DATEDIFF(MINUTE, @dateFrom, @dateTo) / 60.0 / 24.0 / 365.25 * 12 monthsDiff

Все зависит от того, что вы хотите сообщить и какова ваша бизнес-логика. Вы можете сделать дополнительные запросы, например, так:

-- to track a change in days - take off the time portion:
SELECT DATEDIFF(DAY, CAST(@dateFrom AS DATE), CAST(@dateTo AS DATE)) daysDiff
-- to track a change in years - you use the year funtion:
SELECT YEAR(@dateTo) - YEAR(@dateFrom) yearsDiff