使用 SQL 计算以年为单位的确切日期差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23145404/
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
Calculate exact date difference in years using SQL
提问by Squ1rr3lz
I receive reports in which the data is ETLto the DB automatically. I extract and transform some of that data to load it somewhere else. One thing I need to do is a DATEDIFFbut the year needs to be exact (i.e., 4.6 years instead of rounding up to five years.
我收到报告，其中数据ETL自动发送到数据库。我提取并转换了一些数据以将其加载到其他地方。我需要做的一件事是 aDATEDIFF但年份需要准确（即 4.6 年而不是四舍五入到五年。
The following is my script:
以下是我的脚本：
select *, DATEDIFF (yy, Begin_date, GETDATE()) AS 'Age in Years'
from Report_Stage;
The 'Age_In_Years'column is being rounded. How do I get the exact date in years?
该'Age_In_Years'列正在四舍五入。我如何获得以年为单位的确切日期？
回答by Nicholas Carey
All datediff()does is compute the number of period boundaries crossed between two dates. For instance
所有datediff()做的是计算两个日期之间的交叉段边界数。例如
datediff(yy,'31 Dec 2013','1 Jan 2014')
returns 1.
返回 1。
You'll get a more accurate result if you compute the difference between the two dates in days and divide by the mean length of a calendar year in days over a 400 year span (365.2425):
如果以天为单位计算两个日期之间的差异，然后除以 400 年跨度 (365.2425) 中以天为单位的日历年的平均长度，您将获得更准确的结果：
datediff(day,{start-date},{end-date},) / 365.2425
For instance,
例如，
select datediff(day,'1 Jan 2000' ,'18 April 2014') / 365.2425
return 14.29461248— just round it to the desired precision.
return 14.29461248- 只需将其四舍五入到所需的精度。
回答by FumblesWithCode
Have you tried getting the difference in months instead and then calculating the years that way? For example 30 months / 12 would be 2.5 years.
您是否尝试过以月为单位计算差异，然后以这种方式计算年份？例如 30 个月/12 年就是 2.5 年。
Edit: This SQL query contains several approaches to calculate the date difference:
编辑：此 SQL 查询包含几种计算日期差异的方法：
SELECT CONVERT(date, GetDate() - 912) AS calcDate
,DATEDIFF(DAY, GetDate() - 912, GetDate()) diffDays
,DATEDIFF(DAY, GetDate() - 912, GetDate()) / 365.0 diffDaysCalc
,DATEDIFF(MONTH, GetDate() - 912, GetDate()) diffMonths
,DATEDIFF(MONTH, GetDate() - 912, GetDate()) / 12.0 diffMonthsCalc
,DATEDIFF(YEAR, GetDate() - 912, GetDate()) diffYears
回答by Dmitri Rechetilov
I think that division by 365.2425 is not a good way to do this. No division can to this completely accurately (using 365.25 also has issues).
我认为除以 365.2425 不是一个好方法。没有任何部门可以完全准确地做到这一点（使用 365.25 也有问题）。
I know the following script calculates an accurate date difference (though might not be the most speedy way):
我知道以下脚本可以计算准确的日期差异（尽管可能不是最快捷的方式）：
declare @d1 datetime ,@d2 datetime
--set your dates eg:
select @d1 = '1901-03-02'
select @d2 = '2016-03-01'
select DATEDIFF(yy, @d1, @d2) -
CASE WHEN MONTH(@d2) < MONTH(@d1) THEN 1
WHEN MONTH(@d2) > MONTH(@d1) THEN 0
WHEN DAY(@d2) < DAY(@d1) THEN 1
ELSE 0 END
-- = 114 years
For comparison:
比较：
select datediff(day,@d1 ,@d2) / 365.2425
-- = 115 years => wrong!
You might be able to calculate small ranges with division, but why take a chance??
你也许可以用除法计算小范围，但为什么要冒险呢？？
The following script can help to test yeardiff functions (just swap cast(datediff(day,@d1,@d2) / 365.2425 as int) to whatever the function is):
以下脚本可以帮助测试 yeardiff 函数（只需将 cast(datediff(day,@d1,@d2) / 365.2425 as int) 换成任何函数）：
declare @d1 datetime set @d1 = '1900-01-01'
while(@d1 < '2016-01-01')
begin
declare @d2 datetime set @d2 = '2016-04-01'
while(@d2 >= '1900-01-01')
begin
if (@d1 <= @d2 and dateadd(YEAR, cast(datediff(day,@d1,@d2) / 365.2425 as int) , @d1) > @d2)
begin
select 'not a year!!', @d1, @d2, cast(datediff(day,@d1,@d2) / 365.2425 as int)
end
set @d2 = dateadd(day,-1,@d2)
end
set @d1 = dateadd(day,1,@d1)
end
回答by K'Dubb
I have found a better solution. This makes the assumption that the first date is less than or equal to the second date.
我找到了更好的解决方案。这假设第一个日期小于或等于第二个日期。
declare @dateTable table (date1 datetime, date2 datetime)
insert into @dateTable
select '2017-12-31', '2018-01-02' union
select '2017-01-03', '2018-01-02' union
select '2017-01-02', '2018-01-02' union
select '2017-01-01', '2018-01-02' union
select '2016-12-01', '2018-01-02' union
select '2016-01-03', '2018-01-02' union
select '2016-01-02', '2018-01-02' union
select '2016-01-01', '2018-01-02'
select date1, date2,
case when ((DATEPART(year, date1) < DATEPART(year, date2)) and
((DATEPART(month, date1) <= DATEPART(month, date2)) and
(DATEPART(day, date1) <= DATEPART(day, date2)) ))
then DATEDIFF(year, date1, date2)
when (DATEPART(year, date1) < DATEPART(year, date2))
then DATEDIFF(year, date1, date2) - 1
when (DATEPART(year, date1) = DATEPART(year, date2))
then 0
end [YearsOfService]
from @dateTable
date1 date2 YearsOfService
----------------------- ----------------------- --------------
2016-01-01 00:00:00.000 2018-01-02 00:00:00.000 2
2016-01-02 00:00:00.000 2018-01-02 00:00:00.000 2
2016-01-03 00:00:00.000 2018-01-02 00:00:00.000 1
2016-12-01 00:00:00.000 2018-01-02 00:00:00.000 1
2017-01-01 00:00:00.000 2018-01-02 00:00:00.000 1
2017-01-02 00:00:00.000 2018-01-02 00:00:00.000 1
2017-01-03 00:00:00.000 2018-01-02 00:00:00.000 0
2017-12-31 00:00:00.000 2018-01-02 00:00:00.000 0
