我收到的报告中数据已经被自动地进行了ETL到数据库中，我需要从其中提取和转换一些数据来加载到别处。 我需要做的一件事是计算DATEDIFF，但年份需要精确（即4.6年而不是四舍五入为五年）。
以下是我的脚本：
select *, DATEDIFF (yy, Begin_date, GETDATE()) AS 'Age in Years'
from Report_Stage;
“Age_In_Years”列正在四舍五入。如何获得精确的年份日期？
使用SQL计算确切的年份差异日期
24
- J.S. Orris
8
2如果你只需要一个有效数字，可以尝试使用DATEDIFF(dd..)，然后除以365。但这不会考虑闰年。 - Paul Abbott
这只会使其偏差大约0.1左右，对吧？ - J.S. Orris
我用那种方式做仍然四舍五入了。 - J.S. Orris
4尝试除以365.0而不是365，这样可以避免隐式转换为整数。 - Paul Abbott
做到了。谢谢。 - J.S. Orris
显示剩余3条评论
6个回答
32
datediff()函数的作用是计算两个日期之间跨越的周期边界数。例如：
datediff(yy,'31 Dec 2013','1 Jan 2014')
返回1。
如果你计算两个日期之间的差异（以天为单位），并将其除以400年跨度内平均每年的日数（365.2425），则可以得到更准确的结果：
datediff(day,{start-date},{end-date},) / 365.2425
例如，
select datediff(day,'1 Jan 2000' ,'18 April 2014') / 365.2425
返回 14.29461248 — 只需将其四舍五入至期望的精度即可。
- Nicholas Carey
1
我认为这才是真正的情况。越界是一件不同于四舍五入的事情，尽管它可能会产生看起来像是四舍五入的结果。 - jinglesthula
15
你尝试过先获取月份差，然后按照此方法计算年份吗？例如30个月/12就是2.5年。
编辑：这个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
- FumblesWithCode
5
该列仍在四舍五入。 - J.S. Orris
@JeffOrris - ...那么这是否回答了你的问题呢？你接受意味着一件事，但是你的评论又是另一件事。很可能是由于仅执行整数运算而导致的截断（即在您的问题评论中所述的应该是“/12.0”以强制浮点数运算）。@Fumbles-您需要向此类答案添加相关代码，否则应该将其关闭。 - Clockwork-Muse
@FumblesWithCode 回答了我的问题。 - J.S. Orris
当起始日期大于结束日期且两个日期在同一个月份时，
DATEDIFF(MONTH, @start, @end) / 12.0 存在四舍五入问题。 - Shago谢谢@Shago，我已经为此撞了一个小时的桌子。 - Randall.Cummins
12
我认为使用365.2425来做这件事并不是一个好方法。没有任何一种除法可以完全准确地解决这个问题（使用365.25也存在问题）。
我知道以下脚本可以计算出一个准确的日期差异（尽管可能不是最快的方法）：
我知道以下脚本可以计算出一个准确的日期差异（尽管可能不是最快的方法）：
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
与之相比：
select datediff(day,@d1 ,@d2) / 365.2425
-- = 115 years => wrong!
虽然您可以通过除法计算小范围，但为什么要冒险呢？
下面的脚本可以帮助测试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
- Dmitri Rechetilov
1
我发现这个答案对我的用例很有帮助。但是为了明确起见，它仍然只产生整数，这与最初的问题不符，因为那个问题要求更高的精度。 - jslatane
2
您需要计算两个日期之间的年份差，但是当未来日期的"一年中的第几天"小于过去日期时，应将年份差减1。请参考以下代码示例：
SELECT *
,DATEDIFF(YEAR, [Begin_date], [End_Date])
+ CASE WHEN CAST(DATENAME(DAYOFYEAR, [End_Date]) AS INT)
>= CAST(DATENAME(DAYOFYEAR, [Begin_date]) AS INT)
THEN 0 ELSE -1 END
AS 'Age in Years'
from [myTable];
- Abacus
1
这在闰年方面并不完美。如果你用它来计算2019-03-15和2020-03-14之间的差距，它会显示有1年的差异，但理想情况下应该是0年吧？因为2020年是闰年，2月29日增加了一天，影响了年中的天数。 - Paul
1
对我来说，我会计算天数的差异。
Declare @startDate datetime
Declare @endDate datetime
Declare @diff int
select @diff=datediff(day,@startDate,@endDate)
if (@diff>=365) then select '1Year'
if (@diff>=730) then select '2Years'
-----etc
- Khalid
0
我找到了一个更好的解决方案。这个方案假设第一个日期小于或等于第二个日期。
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
- K'Dubb
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接